Post reply

How do I get the all columns of a row for the row with the highest value of a column

  • July 15, 2012 at 8:43 am

    #259468

    I have a table that has many columns. I want to return all the columns of the rows with the greatest ID for the unit id.

    example

    ID, unit

    1,33

    2,33

    3,25

    4,55

    5,55

    the result I am looking for is:

    2,33

    3,25

    5,55

    I hope I explained it well.

  • July 15, 2012 at 10:30 am

    #1512880

    Is this what you desire ?

    CREATE TABLE #T(ID INT, Unit INT)

    INSERT INTO #t

    SELECT 1,33 UNION ALL

    SELECT 2,33 UNION ALL

    SELECT 3,25 UNION ALL

    SELECT 4,55 UNION ALL

    SELECT 5,55

    SELECT MAX(ID)AS 'id', Unit FROM #T group by unit

    Results:

    Id Unit

    3 25

    2 33

    5 55

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • July 15, 2012 at 11:35 am

    #1512891

    SELECT <other columns> FROM

    (SELECT

    <other columns>,

    ROW_NUMBER() OVER (PARTION BY unit ORDER BY ID DESC) AS RowNo

    FROM <Some Table>

    )sub

    WHERE RowNo = 1

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • July 16, 2012 at 3:42 am

    #1512975

    WITH CTE AS

    (SELECT *, row_number() over (partition by unit order by id desc) as rowno

    from tablename

    )

    select * from CTE where rowno=1;

  • July 16, 2012 at 7:29 am

    #1513059

    -- Sample code

    CREATE TABLE #T(ID INT, Unit INT)

    INSERT INTO #t

    SELECT 1,33 UNION ALL

    SELECT 2,33 UNION ALL

    SELECT 3,25 UNION ALL

    SELECT 4,55 UNION ALL

    SELECT 5,55

    -- Query

    SELECT MAX(ID) ID, MAX(Unit) Unit

    FROM #t

    GROUP BY Unit

    "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

  • July 16, 2012 at 3:23 pm

    #1513343

    I hope I am repling correctly. Im an old dog trying to learn new tricks. The replies did work on my first problem. The second problem involved more columns with the same concept. I decided not to post that one but to forge on trying to figure some things out. With all the replies I got fixed up on the other one. Thank you very much for your help. Hope I can help someone in the future.

  • July 16, 2012 at 4:39 pm

    #1513358

    groads2 (7/16/2012)

    I hope I am repling correctly. Im an old dog trying to learn new tricks. The replies did work on my first problem. The second problem involved more columns with the same concept. I decided not to post that one but to forge on trying to figure some things out. With all the replies I got fixed up on the other one. Thank you very much for your help. Hope I can help someone in the future.

    You can help now. Post your solution so that others can learn if they read this thread. 🙂

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 7 posts - 1 through 6 (of 6 total)

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