autogenerate identity column

• scottichrosaviakosmos

  SSCommitted

  Points: 1889

  More actions

  September 6, 2010 at 3:35 am

  #223461

  i have a qurey which i am creating joining 4 tables by join .

  now when i get resultset i want a new column to add like Srno(serial no.)

  which will display the numbers for each row, as if it behaves like an identity column. how to add this column to my already existing query.

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  September 6, 2010 at 3:40 am

  #1216823

  ROW_NUMBER Function will do the trick for you..

  HEre are some articles on that

  ROW_NUMBER(): An Efficient Alternative to Subqueries

  Paging and Versioning Using ROW_NUMBER() [/url]

  ROW_NUMBER MSDN link

• ColdCoffee

  SSC-Dedicated

  Points: 39972

  More actions

  September 6, 2010 at 3:42 am

  #1216825

  A small example:

  ;WITH Tens AS

  (

  SELECT 1 N UNION ALL

  SELECT 1 UNION ALL

  SELECT 1 UNION ALL

  SELECT 1 UNION ALL

  SELECT 1 UNION ALL

  SELECT 1 UNION ALL

  SELECT 1 UNION ALL

  SELECT 1 UNION ALL

  SELECT 1 UNION ALL

  SELECT 1

  )

  SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RN FROM Tens

• Madhivanan-208264

  SSCertifiable

  Points: 7516

  More actions

  September 7, 2010 at 7:09 am

  #1217305

  See also what you can do with row_number() function

  http://beyondrelational.com/blogs/madhivanan/archive/2007/08/27/multipurpose-row-number-function.aspx

  ---

  Madhivanan

  Failing to plan is Planning to fail