How to have a line number without cursor

• zoltix

  SSC Eights!

  Points: 813

  More actions

  January 15, 2003 at 3:08 am

  #159403

  Hi,

  I have a general question about sql. In oracle, it exist a rownumber function that we allow see the line number, I would like to have same thing in mssql without cursor. Is it possible?

  Ex

  Select …… from …….

  Go

  N name ……

  1 qdff

  2 aze

  … ….

  N Zxxxx

  Thank in advance

• Andy Jones, DBA

  Hall of Fame

  Points: 3641

  More actions

  January 15, 2003 at 4:07 am

  #446357

  No, there is no built in function. An alternative to a cursor is to insert the result-set into a temp table which has an identity field (1,1). Then select from the temp table and your result-set will be numbered as you want.

  Regards,

  Andy Jones

  .

• Greg Larsen

  SSC-Insane

  Points: 20966

  More actions

  January 15, 2003 at 8:19 am

  #446378

  Here is another way to set a row number:

  use northwind

  go

  select count(*) row_num, a.customerid,a.employeeid from

  (select customerid, employeeid from Northwind.dbo.orders group by customerid,employeeid) a,

  (select customerid, employeeid from Northwind.dbo.orders group by customerid,employeeid) b

  where a.customerid+cast(a.employeeid as char) > b.customerid+cast(b.employeeid as char)

  group by a.customerid, a.employeeid

  order by 2,3

  Gregory Larsen, DBA

  If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples

  Gregory A. Larsen, MVP