SELECT max date from an inner join relation

Question

I have 2 tables, Staff and updateStaff.

Staff:

Sid  Sname       
 ---|--------|
 1  | test1  |
 2  | test2  | 
 3  | test3  | 
 4  | test4  | 
 5  | test5  | 

updateStaff:

Sid  Sprice    SDate    STime
---|--------|----------|--------|
1  | 150    |2015/10/09|6:35:00 |
2  | 250    |2015/10/10|5:21:00 |
3  | 75     |2015/11/11|17:30:00|
3  | 95     |2015/11/11|18:21:00|
4  | 300    |2015/12/12|2:25:00 |

I need result shows as:

Sid   SDate    STime     Sname   | Sprice     |
---|----------|--------|---------|------------
1  |2015/10/09|6:35:00 |test1    |150        |
2  |2015/10/10|5:21:00 |test2    |250        |
3  |2015/11/11|17:30:00|test3    |95         |
3  |2015/11/11|18:21:00|test3    |300        |
4  |2015/12/12|2:25:00 |test5    |NULL       |

In the other case, my below code show me both staff Id 3 on 2015/11/11 date.

SELECT  R.SId ,R.SName,R.Sprice
FROM (SELECT  Staff.SId ,Staff.SName,Sprice,updateStaff.SDate
      FROM Staff
        LEFT JOIN updateStaff ON Staff.SId = updateStaff.SId ) AS R
WHERE R.date = (SELECT MAX(date) FROM updateStaff WHERE updateStaff.SId =R.SId)
ORDER BY R.SId , R.SName

I need only the last staff's price order by date, time.

Answer 1

I'm not sure about sql server ce syntax, so I'm pretty sure there is a better way of doing it but you can do this:

SELECT  R.SId ,R.SName,R.Sprice
FROM (SELECT  Staff.SId ,Staff.SName,Sprice,updateStaff.SDate,updateStaff.stime
      FROM Staff
        LEFT JOIN updateStaff ON Staff.SId = updateStaff.SId ) AS R
WHERE R.stime = (SELECT MAX(stime) FROM updateStaff us WHERE us.SId =R.SId
      and us.sdate =(select max(sdate) from updateStaff us2 where us2.sid = us.sid))
ORDER BY R.SId , R.SName