select max

Question

select max

YarHad

Mr or Mrs. 500

Points: 550
More actions
August 22, 2012 at 9:24 am

#265651

Hi All,
I have two tables as follows
salary table with columns (salary_id, student_id, salary, from_date)
student table with columns (student_id, firstname)
I am trying to retun (s.student_id, firstname, salary and max(from_date))
(students can have more than one entry in the salary table
however I want to return the row associated with the latest date because this will be the current salary )
when I run...
select
s.student_id, salary, max(from_date), firstname
from student
inner join salary
on student.student_id = salary.student_id
group by
student.student_id, salary, firstname
this does not return the row associated with the latest date. It returns rows with every salary.
I cannot ommit the salary from the 'group by'
How can I write it so that it returns just the row associated with the latest date
Thanks

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

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

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 1

A cte with a row_number should help you. I can't test since you don't have sample data, but let me know if it does what you need and if you understand what it is doing.

with cte as

(

select stu.student_id, sal.salary, stu.firstname, ROW_NUMBER() over (partition by sal.student_id order by sal.from_date desc) rn

from salary sal

join student stu on stu.student_id = sal.student_id

)

select *

from cte

where rn = 1

OTF SSCertifiable Points: 6054 More actions · Answer 2

YarHad (8/22/2012)
Hi All,
I have two tables as follows
salary table with columns (salary_id, student_id, salary, from_date)
student table with columns (student_id, firstname)
I am trying to retun (s.student_id, firstname, salary and max(from_date))
(students can have more than one entry in the salary table
however I want to return the row associated with the latest date because this will be the current salary )
when I run...
select
s.student_id, salary, max(from_date), firstname
from student
inner join salary
on student.student_id = salary.student_id
group by
student.student_id, salary, firstname
this does not return the row associated with the latest date. It returns rows with every salary.
I cannot ommit the salary from the 'group by'
How can I write it so that it returns just the row associated with the latest date
Thanks

I was going to say this sounds a bit like homework and you should read up on Widowing Functions, such as Row_Number() which might help you but it appears a possible solution has already been given 🙂

YarHad Mr or Mrs. 500 Points: 550 More actions · Answer 3

SSC_Addicted,

Thanks you very much for your reply.

Today is the first I am coming across CTE and what it can and be used for.

Thanks a whole lot

I am going to research it more

roryp 96873 SSCertifiable Points: 6649 More actions · Answer 4

Sounds good. Post back if you need any clarification or additional help. This link would be a good place to start.

YarHad Mr or Mrs. 500 Points: 550 More actions · Answer 5

SSC-Addicted,

Once again (From the deepest corner of my heart!)

Thanks