DISTINCT is not working with ROW_NUMER

  • 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

    ---

  • 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

  • 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

  • Thanks guyz for your reply.

  • Glad we could help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply