Monday, March 19, 2012

Adding Serial No in the Query Resultset

Hi to All!
Can any one help me in generating serial no in the query result?
e.g. i write a query it gives me 500 rows.
select name,fathername from abc
name FatherName
Harry David
Sarah Nenry
.
.
.
.
i want that reslut should come like that
S-no name FatherName
1 Harry David
2 Sarah Nenry
.
.
.
.
500 Farid Masood
how can i add this sequence no in query i have no S-no column?
Regards
Thanx
*** Sent via Developersdex http://www.examnotes.net ***1. Create a temp table with an Identity column
2. Insert your values into the temp table
3. Select * From the temp table and return that resultset.
Greg Jackson
PDX, Oregon|||http://www.aspfaq.com/2427
"Ghulam Farid" <gfaryd@.yahoo.com> wrote in message
news:uBPoaM2IGHA.2696@.TK2MSFTNGP14.phx.gbl...
> Hi to All!
> Can any one help me in generating serial no in the query result?
> e.g. i write a query it gives me 500 rows.
> select name,fathername from abc
> name FatherName
> Harry David
> Sarah Nenry
> .
> .
> .
> .
> i want that reslut should come like that
> S-no name FatherName
> 1 Harry David
> 2 Sarah Nenry
> .
> .
> .
> .
> 500 Farid Masood
> how can i add this sequence no in query i have no S-no column?
> Regards
> Thanx
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Line numbering is an issue for the front end and has nothing to do with
the RDBMS. You can use a stinking dirty kludge with a proprietary
IDENTITY if you do not care about proper coding.
Have you ever had a software engineering course or read a book on
Software Engineering?|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23YwLvk5IGHA.3176@.TK2MSFTNGP12.phx.gbl...

> http://www.aspfaq.com/2427
'Be sure to read KB #186133 for Microsoft's official word'
(How to dynamically number rows in a SELECT Transact-SQL statement)
Applies to 2005...?..well do the people that write this stuff read BOL?
Perhaps they want to keep some things a secret:)
www.rac4sql.net|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1138413114.379715.161790@.g47g2000cwa.googlegroups.com...
> Line numbering is an issue for the front end and has nothing to do with
> the RDBMS. You can use a stinking dirty kludge with a proprietary
> IDENTITY if you do not care about proper coding.
> Have you ever had a software engineering course or read a book on
> Software Engineering?
Would you feel better substituting ranking for line numbering?
And I was having a hard time using 'stinking dirty kludge' in
a sentence.
Did you write for 'Laugh In'? :)|||>> Would you feel better substituting ranking for line numbering? <<
That is fine; ranking as a rule while putting a number on the output of
an un-ordred cursor is absurd and unrepeatable.
But do you have a bigger problem using it in a program :)? You
should.
Two gags only. And I do not remember what they were. They paid $50
for the two.|||I've posted something in the private groups so hopefully they will update to
reflect all the new bits that they brought in for SQL Server 2005.
Eg...
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"05ponyGT" <nospam@.nospam> wrote in message
news:ejYR1SHJGHA.1188@.TK2MSFTNGP14.phx.gbl...
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message
> news:%23YwLvk5IGHA.3176@.TK2MSFTNGP12.phx.gbl...
>
> 'Be sure to read KB #186133 for Microsoft's official word'
> (How to dynamically number rows in a SELECT Transact-SQL statement)
> Applies to 2005...?..well do the people that write this stuff read BOL?
> Perhaps they want to keep some things a secret:)
> www.rac4sql.net
>|||select row_number() over ( order by name ), name
from sys.objects
order by name
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in message
news:ui7ET6LJGHA.3408@.TK2MSFTNGP12.phx.gbl...
> I've posted something in the private groups so hopefully they will update
> to reflect all the new bits that they brought in for SQL Server 2005.
> Eg...
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "05ponyGT" <nospam@.nospam> wrote in message
> news:ejYR1SHJGHA.1188@.TK2MSFTNGP14.phx.gbl...
>

No comments:

Post a Comment