sql server programming: Help with query. -- sql server programming

Help with query.

ChrisR
12/27/2006 8:34:37 PM

sql server programming: In adventureWorks, this query is producing almost what I want to do in my
real DB.

select top 10 e.employeeId, vacationHours,

max(eph.rateChangeDate) as rateChangeDate, rate

from humanResources.employee e

inner join humanResources.employeePayHistory eph

on e.employeeId = eph.employeeId

group by e.employeeId, rate, vacationHours

order by 1

The difference of what I want though is to only have 1 row for each
employeeID, and that row should contain the row with the highest
rateChangeDate for that employeeID. So instead of this:

1 21 1996-07-31 00:00:00.000 12.45
2 42 1997-02-26 00:00:00.000 13.4615
3 2 1997-12-12 00:00:00.000 43.2692
4 48 1998-01-05 00:00:00.000 8.62
4 48 2000-07-01 00:00:00.000 23.72
4 48 2002-01-15 00:00:00.000 29.8462
5 9 1998-01-11 00:00:00.000 25.00
6 40 1998-01-20 00:00:00.000 24.00
6 40 1999-08-16 00:00:00.000 28.75
6 40 2002-06-01 00:00:00.000 37.50

I should have this:

1 21 1996-07-31 00:00:00.000 12.45
2 42 1997-02-26 00:00:00.000 13.4615
3 2 1997-12-12 00:00:00.000 43.2692
4 48 2002-01-15 00:00:00.000 29.8462
5 9 1998-01-11 00:00:00.000 25.00
6 40 2002-06-01 00:00:00.000 37.50

Row's 7 - 10 would then go here.

TIA, ChrisR

RE: Help with query.

JayKon
12/27/2006 10:46:00 PM

I'm a bit rusty, but I beleive you need a HAVING clause after the GROUP BY.

HAVING(max(eph.rateChangeDate))

[quoted text, click to view]

Re: Help with query.

markc600 NO[at]SPAM hotmail.com
12/28/2006 3:10:08 AM

with cte
as (select e.EmployeeID,
e.VacationHours,
eph.RateChangeDate,
eph.Rate,
rank() over(partition by e.EmployeeID order by
eph.RateChangeDate desc) as rn
from HumanResources.Employee e
inner join HumanResources.EmployeePayHistory eph
on e.EmployeeID = eph.EmployeeID)
select top 10 EmployeeID,
VacationHours,
RateChangeDate,
Rate
from cte
where rn=1
order by EmployeeID

If an employee has two (or more) rows with the same highest
rateChangeDate
you will still get both rows for that employee. In this case you'll
have to be more specific in the rank().

Re: Help with query.

Uri Dimant
12/28/2006 8:44:01 AM

CrisR
select TOP 10 e.EmployeeID, rateChangeDate,VacationHours,Rate

from HumanResources.Employee e

inner join ( SELECT EmployeeID,MAX(eph.RateChangeDate) as rateChangeDate,

MAX(Rate) Rate

FROM HumanResources.EmployeePayHistory eph

GROUP BY eph.EmployeeID ) AS Der

on e.EmployeeID = Der.EmployeeID

order by 1

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.