Rakhitha Nimesh Rakhitha Nimesh - 3 months ago 9
SQL Question

SQL Orderby date and get results per user

I have a database table with following fields

ID User ID Comment Date
1 1 Test 2016-08-07
2 2 Test2 2016-08-17
3 1 Test3 2016-08-16
4 2 Test4 2016-08-15


First we get the latest date and then get all records for the user with that date. Then next latest date and all records for the user with that date. So the output is

2 2 Test2 2016-08-17
4 2 Test4 2016-08-15
3 1 Test3 2016-08-16
1 1 Test 2016-08-07


Let me know if anyone can provide sql query

Thanks

Answer

Try this

select user.*
from user
join(
  select UserID, Max(Date)  md
  from user 
  group by UserId
  ) dts on dts.UserID = user.UserID
order by dts.md desc, user.UserId, user.Date desc