SELECT WITH ONE FIELD THAT INCREMENTS

• Raymond van Laake

  SSCarpal Tunnel

  Points: 4212

  More actions

  August 20, 2010 at 5:18 am

  #226395

  Hi,

  Maybe a bit difficult to explain, but here it is: I need a select statement, with one field that increments.

  For example:

  SELECT name, country FROM persons WHERE lastname='Jones'

  would result in:

  [font="Courier New"]

  John USA

  Pete USA

  Mary Canada

  [/font]

  I need something like:

  SELECT cntr, name, country FROM persons WHERE lastname='Jones'

  would result in:

  [font="Courier New"]

  1 John USA

  2 Pete USA

  3 Mary Canada

  [/font]

  Anyone?

  Thanks!

• Nick_UK

  SSCommitted

  Points: 1898

  More actions

  August 20, 2010 at 5:39 am

  #1209588

  you could use the row_number function to do this for you

  SELECT ROW_NUMBER() OVER(ORDER BY name) as [cntr], name, country FROM persons WHERE lastname='Jones'

  http://msdn.microsoft.com/en-us/library/ms186734.aspx[/b]

• Raymond van Laake

  SSCarpal Tunnel

  Points: 4212

  More actions

  August 20, 2010 at 5:43 am

  #1209589

  tHX!!!!!