Row Number - 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

General SQL Server Forums

New to SQL Server Programming

Row Number

Author

Topic

Sep410
Posting Yak Master

117 Posts

Posted - 2008-06-11 : 17:37:42

Hi,

I need to add row number to my query result and I know how to do that in SQl2005 but the row number function is not working in 2000.Please help me to figure out how should I do that in SQl2000.The query that I have is kind of a big query with 162 columns. It is coming from another view.

Thanks.

Sep

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 00:58:14

Can you post the table structures with some sample data?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-12 : 09:45:16

Where do you want to show data?

Madhivanan

Failing to plan is Planning to fail

Sep410
Posting Yak Master

117 Posts

Posted - 2008-06-12 : 10:43:16

SELECT 'CONV' AS VLEDUS, '' AS VLEDTY, '' AS VLEDSQ, '1' AS VLEDTN, '' AS VLEDCT, 'Row Number' AS VLEDLN, '' AS VLEDTS,
'' AS VLEDFT, (CONVERT(int, RIGHT('000' + CONVERT(varchar(3), DATEDIFF(year, 0, CONVERT(VARCHAR(8), GETDATE(), 1))), 3)
+ RIGHT('000' + CONVERT(varchar(3), DATEDIFF(day, DATEADD(year, DATEDIFF(year, 0, CONVERT(VARCHAR(8), GETDATE(), 1)), 0), CONVERT(VARCHAR(8),
GETDATE(), 1))), 3))) AS VLEDDT, 'R' AS VLEDER, '' AS VLEDDL, '0' AS VLEDSP, 'A' AS VLEDTC, '1' AS VLEDTR,
(Select * from tblRun )+1 AS VLEDBT, '' AS VLEDGL, '' AS VLPANP, DT .C

FROM
dbo.vStaff vStaff CROSS JOIN
(SELECT ' .01 ' AS C
UNION
SELECT ' - .01 ' AS D) DT
WHERE (vStaff.status = 'A' OR
vStaff.status = 'AL')and (vStaff.classification='Hourly')
GROUP BY vStaff.ClientID,DT .C
order by vstaff.clientID

This is part of my query.
Please help me.

Sep

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-06-12 : 10:45:57

quote:
Originally posted by Sep410

SELECT 'CONV' AS VLEDUS, '' AS VLEDTY, '' AS VLEDSQ, '1' AS VLEDTN, '' AS VLEDCT, 'Row Number' AS VLEDLN, '' AS VLEDTS,
'' AS VLEDFT, (CONVERT(int, RIGHT('000' + CONVERT(varchar(3), DATEDIFF(year, 0, CONVERT(VARCHAR(8), GETDATE(), 1))), 3)
+ RIGHT('000' + CONVERT(varchar(3), DATEDIFF(day, DATEADD(year, DATEDIFF(year, 0, CONVERT(VARCHAR(8), GETDATE(), 1)), 0), CONVERT(VARCHAR(8),
GETDATE(), 1))), 3))) AS VLEDDT, 'R' AS VLEDER, '' AS VLEDDL, '0' AS VLEDSP, 'A' AS VLEDTC, '1' AS VLEDTR,
(Select * from tblRun )+1 AS VLEDBT, '' AS VLEDGL, '' AS VLPANP, DT .C

FROM
dbo.vStaff vStaff CROSS JOIN
(SELECT ' .01 ' AS C
UNION
SELECT ' - .01 ' AS D) DT
WHERE (vStaff.status = 'A' OR
vStaff.status = 'AL')and (vStaff.classification='Hourly')
GROUP BY vStaff.ClientID,DT .C
order by vstaff.clientID

This is part of my query.
Please help me.

Sep

This may lead to error if there is schema change

Madhivanan

Failing to plan is Planning to fail

Sep410
Posting Yak Master

117 Posts

Posted - 2008-06-12 : 10:49:05

That part is counting how many times the query was used till now.

Sep

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 12:52:08

did you mean SELECT COUNT(*) FROM tblRun?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2008-06-12 : 13:48:22

An general way of generating the row number in 2000 is as below


SELECT        (SELECT COUNT(i.au_id) 
               FROM pubs..authors i 
               WHERE i.au_id >= o.au_id ) AS RowID, 
              au_fname + ' ' + au_lname AS 'Author name'
FROM          pubs..authors o
ORDER BY      RowID

but this is not at all recommended as it has lots of level of iterations.

it would be much better if you can generate the rownumber in your front end where you are planning to show the data.

you can replace the above query in your query, if you have any unqiue column in your table.

Chirag

http://www.chirikworld.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources