Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 returning entire rows with max value

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-06-15 : 08:29:45
barkie writes "We recently converted our salary history to reflect hourly wage not bi-weekly wage but kept all the old data for historical purposes. The application uses an id for the staff member and a sub_id to reflect the last entered value. The salary rate could go up or down over time. So, I'd like a query that returns the last entered transaction [ max(sub_id) seems logical) and the other values in the row too.

table salhistory
id = integer
sub_id = integer
sal_rate = currency

id sub_id sal_rate (return this row?)
1 56 2218
1 122 2232
1 3613 22.15 y
12 88 2322
12 133 2100
12 3720 22.33
12 3752 21.17 y
66 156 10.10 y
3220 1810 2266
3220 1922 2816
3220 5620 62.19 y

==================================
I know you are on top of this one...
barkie"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-15 : 08:35:35
[code]select z.id,
salhistory.sub_id,
salhistory.sal_rate
from (
SELECT id,
MAX(sub_id) subid
FROM salhistory
) z
inner join salhistory on salhistory.id = z.id and salhistory.subid = z.sub_id
[/code]

Peter Larsson
Helsingborg, Sweden
Go to Top of Page
   

- Advertisement -