Selecting Max(ID)

  • Let's say I have a simple table as follows:

    declare @TestTable table (

    ID int,

    EmployeeID int,

    Salary varchar(12)

    )

    insert into @TestTable (

    ID,

    EmployeeID,

    Salary

    )

    values

    (1, 5, '1000'),

    (2, 6, '2000'),

    (3, 7, '1000'),

    (4, 5, '2000'),

    (5, 6, '2500')

    How can I select the Max(ID) for each EmployeeID?

    The results would be as follows:

    ID__EmployeeID__Salary

    4..........5..............2000

    5..........6..............2500

    3..........7..............1000

    Seems like it should be really simple, but I am a struggling newbie.

    Thanks.

  • Think this might do it.

    ;WITH cte

    AS (SELECT Row_Number() OVER(PARTITION BY EmployeeID ORDER BY ID DESC) AS rn,ID,EmployeeID,Salary

    FROM @TestTable)

    SELECT * FROM cte WHERE Rn = 1

    Results:

    rnIDEmployeeIDSalary

    145 2000

    156 2500

    137 1000

    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]

  • declare @TestTable table (

    ID int,

    EmployeeID int,

    Salary varchar(12)

    )

    insert into @TestTable (

    ID,

    EmployeeID,

    Salary

    )

    values

    (1, 5, '1000'),

    (2, 6, '2000'),

    (3, 7, '1000'),

    (4, 5, '2000'),

    (5, 6, '2500')

    SELECT tt.* FROM @TestTable tt INNER JOIN (

    SELECT MAX(id) id,EmployeeID FROM @TestTable GROUP BY EmployeeID) t

    ON tt.EmployeeID = t.EmployeeID

    AND tt.id= t.id

    ORDER BY tt.id

    output

    IDEmployeeIDSalary

    371000

    452000

    562500

  • Perfect!!

    Thanks for the responses, much appreciated.

  • bubs,

    This may be an irrelevant question, but how many rows are you planning on running against? If this is a one time shot it's not a big deal, but if this is code going into a production environment, you should know that the performance speed of those two solutions may differ considerably at different volumes. (You'll get correct results either way.)

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Which one will perform better?

    It is going into a production environment. There are not very many rows now, but it will grow over time. There could potentially be several thousand rows.

  • It depends. The Row_Number() technique wins at low volumes and the MAX() function performs better as you start moving up to to tens of thousands of rows. There is at least one more way to do it involving selecting the top(1) row for a sorted group, which I would recommend if you are going to be processing a million or more rows.

    Take a look at this article[/url] and you'll see all three techniques compared. The examples in the article are based on a maximum date rather than a maximum ID, but the principal is the same.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • Even this worked ...

    (select max(id),employeeid,max(salary) from sc2

    group by employeeid)

  • That's true given the data supplied. But my understanding is that the requirement was to get the salary from the row with the max(id), whether or not that happened to be the maximum salary.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Viewing 9 posts - 1 through 8 (of 8 total)

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