July 20, 2011 at 5:31 pm
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.
July 20, 2011 at 7:01 pm
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:
rn ID EmployeeID Salary
1 4 5 2000
1 5 6 2500
1 3 7 1000
July 21, 2011 at 2:38 am
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
ID EmployeeID Salary
3 7 1000
4 5 2000
5 6 2500
July 21, 2011 at 10:39 am
Perfect!!
Thanks for the responses, much appreciated.
July 21, 2011 at 2:11 pm
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
July 21, 2011 at 2:59 pm
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.
July 21, 2011 at 3:17 pm
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
July 23, 2011 at 10:19 pm
Even this worked ...
(select max(id),employeeid,max(salary) from sc2
group by employeeid)
July 25, 2011 at 1:40 pm
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