DISTINCT is not working with ROW_NUMER

• SqlUser-529296

  SSCarpal Tunnel

  Points: 4072

  More actions

  June 24, 2009 at 4:47 am

  #132191

  Hi All,

  Can guyz tell me, why DISTINCT is not working in this context. How can it will be possible in the ROW_NUMBER().

  DECLARE @test-2 TABLE (fldCID varchar(8))

  INSERT INTO @test-2 (fldCID)

  SELECT '1.1' UNION ALL

  SELECT '1.1' UNION ALL

  SELECT '1.1' UNION ALL

  SELECT '2.2' UNION ALL

  SELECT '2.2' UNION ALL

  SELECT '2.2' UNION ALL

  SELECT '3.3' UNION ALL

  SELECT '3.3' UNION ALL

  SELECT '3.3' UNION ALL

  SELECT '4.4' UNION ALL

  SELECT '4.4' UNION ALL

  SELECT '4.4'

  SELECT DISTINCT

  ROW_NUMBER() OVER (ORDER BY fldCID),

  fldCID

  FROM @test-2

  ---

• justanewone

  Ten Centuries

  Points: 1398

  More actions

  June 24, 2009 at 4:57 am

  #1014537

  It's because the combination of fldCID and the ROW_NUMBER() are not distinct together, Try this...

  SELECT fldCID, ROW_NUMBER() OVER(ORDER BY fldCID) FROM (SELECT DISTINCT fldCID FROM @test-2) AS X

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  June 24, 2009 at 5:01 am

  #1014539

  I think ROW_NUMBER is the wrong function in this case. Try RANK or DENSE_RANK:

  SELECT DISTINCT

  DENSE_RANK() OVER (ORDER BY fldCID),

  fldCID

  FROM @test-2

• SqlUser-529296

  SSCarpal Tunnel

  Points: 4072

  More actions

  June 24, 2009 at 5:12 am

  #1014545

  Thanks guyz for your reply.

• Florian Reischl

  SSC-Dedicated

  Points: 37301

  More actions

  June 24, 2009 at 5:14 am

  #1014546

  Glad we could help