Serial Numbers in SQL Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Serial Numbers in SQL Query

Author

Topic

gundavarapu
Starting Member

8 Posts

Posted - 2003-09-02 : 01:21:53

Hi All,

How can we get the serial numbers for the result set of a query?
For example

Select StudName. Marks from Student

statement return 3 rows say,

AAA 86
BBB 49
CCC 67

i'd like to include a virtual column, which is not in the table, with the serial numbers for the rows like.,

1 AAA 86
2 BBB 49
3 CCC 67
...

I hope you understood.

Thanks

Regards,
Sidhu

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2003-09-02 : 02:02:43

Hi Sidhu,

based on your sample data you could use something like


CREATE TABLE ABC(
a char(3),
b int)
GO
INSERT INTO abc (a,b) VALUES('AAA',86)
INSERT INTO abc (a,b) VALUES('BBB',49)
INSERT INTO abc (a,b) VALUES('CCC',67)

SELECT * FROM abc
SELECT (SELECT COUNT(*) FROM abc t1 WHERE t1.a <= t2.a) AS MyID, t2.a, t2.b
FROM abc t2
ORDER BY 1

On general this link should help
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q186/1/33.asp&NoWebContent=1

Cheers,
Frank

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-09-02 : 05:02:41

Hi,

If you see the following link, you can get clear idea.

http://sqlteam.com/item.asp?ItemID=1491

Thanks

":-) IT Knowledge is power :-)"

gundavarapu
Starting Member

8 Posts

Posted - 2003-09-03 : 08:48:26

Thanks to both of you.

I figured the problem myself and came up with this solution


SELECT count(*) SerialNumber,
       a.strBookId
     FROM LibBooks a join
          LibBooks b
          on a.strBookId >= b.strBookId
     group by a.strBookId
     order by a.strBookId

Regards,
Sidhu

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-09-03 : 10:21:18

Hi Sidhu,

Are you seen that link? If u rdy to use #temp table, create an #temp table with an IDENTITY column, which starts from 1 and increment by 1. Your problem will be solved.

Thanks

":-) IT Knowledge is power :-)"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources