Post reply

Get the maximum Row number for the duplicate valued rows

  • May 20, 2015 at 2:34 pm

    #398485

    Hi All,

    I have a table which dont any Identity column. The data in the table has duplicate values, some rows are totally identical. I just want to select the row which has maximum Row Id/Row number if I am getting multiple rows in my select statement

    How to achieve this please help

  • May 20, 2015 at 2:56 pm

    #1799491

    If the rows are identical, how can can you have a "maximum"?

    Are you trying to delete duplicate rows or simply trying to grab just 1 row from a set of duplicates, when duplicates are present?

    In either case, please post DDL, some test data and the desired outcome based on that test data, so that we have something to test with.

  • May 20, 2015 at 3:40 pm

    #1799498

    Ditto everything Jason said. Some DDL and an example of what you are doing/trying to do would help.

    Perhaps you can use this code as an example of how to solve your problem.

    DECLARE @table TABLE (c1 char(1) not null);

    INSERT @table VALUES ('a'),('a'),('b'),('c'),('d'),('d'),('d'),('e'),('f');

    -- table with multiple rows

    SELECT * FROM @table;

    -- how to get a distinct group of values

    WITH x AS

    (

    SELECT

    group_id= ROW_NUMBER() OVER (partition by c1 ORDER BY (select null)),

    c1

    FROM @table

    )

    SELECT *

    FROM x

    WHERE group_id = 1;

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 3 posts - 1 through 2 (of 2 total)

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