Row number returned in resultset

• Scott Thornton-407727

  SSCrazy

  Points: 2271

  More actions

  September 23, 2008 at 12:21 am

  #191220

  Hi,

  I need to return a row number in a query result set eg:

  if there were three rows in the table with one column eg species( type )

  1 cat

  2 dog

  3 bird

  ( has to work in 2000 as well )

  The only soultion I have come up with is to create a temp table with an identity field, and insert my result set into it, then select * from that to get resultset in addtion to 1,2,3 .... n

  But this approach seems a bit complex.

  Know what I mean?

  Any solutions?

  I actually need a field to create a group on in Crystal reports.. so I can create a new group every x number of records using quey.recordnumber \ x as a crystal formula.

  Thanks,

• Madhivanan-208264

  SSCertifiable

  Points: 7516

  More actions

  September 23, 2008 at 12:59 am

  #875440

  Scott Thornton (9/23/2008)

  ---

  Hi,

  I need to return a row number in a query result set eg:

  if there were three rows in the table with one column eg species( type )

  1 cat

  2 dog

  3 bird

  ( has to work in 2000 as well )

  The only soultion I have come up with is to create a temp table with an identity field, and insert my result set into it, then select * from that to get resultset in addtion to 1,2,3 .... n

  But this approach seems a bit complex.

  Know what I mean?

  Any solutions?

  I actually need a field to create a group on in Crystal reports.. so I can create a new group every x number of records using quey.recordnumber \ x as a crystal formula.

  Thanks,

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

  But you should do this in Crystal Reports

  ---

  Madhivanan

  Failing to plan is Planning to fail

• Adrian Nichols-360275

  SSCertifiable

  Points: 7694

  More actions

  September 23, 2008 at 4:26 am

  #875535

  Hi,

  If you're going to do this in Crystal, I can't remember if this is correct or not, but I have a nagging feeling that there is a special field called Record Number that you can use? Maybe in a formula or something?

  Fuzzy memory. :ermm:

  ---

  
  Ade

  A Freudian Slip is when you say one thing and mean your mother.
  For detail-enriched answers, ask detail-enriched questions...[/url]

• SqlUser-529296

  SSCarpal Tunnel

  Points: 4072

  More actions

  September 23, 2008 at 5:29 am

  #875557

  Hi Scott,

  Use ROW_NUMBER function like the below one. I think you will get.

  DROP TABLE #Test1

  CREATE TABLE #Test1 (fldName varchar(8))

  INSERT INTO #Test1 (fldName)

  SELECT 'Cat'

  UNION ALL

  SELECT 'Dog'

  UNION ALL

  SELECT 'Bird'

  UNION ALL

  SELECT 'Tiger'

  SELECT *,ROW_NUMBER() OVER (ORDER BY fldName) FROM #Test1

  ---

• Scott Thornton-407727

  SSCrazy

  Points: 2271

  More actions

  September 23, 2008 at 4:19 pm

  #876058

  Adrian Nichols (9/23/2008)

  ---

  Hi,

  If you're going to do this in Crystal, I can't remember if this is correct or not, but I have a nagging feeling that there is a special field called Record Number that you can use? Maybe in a formula or something?

  Fuzzy memory. :ermm:

  Hi, you cannot use RecordNumber in a formula, and then group on that formula. At least not Crytsal9.

  Tried that :>

• Scott Thornton-407727

  SSCrazy

  Points: 2271

  More actions

  September 23, 2008 at 4:22 pm

  #876060

  SELECT *,ROW_NUMBER() OVER (ORDER BY fldName) FROM #Test1

  pretty sure that doesn't work in 2000 :>