CODEWITHSUNDEEP
sqlsql-servert-sqldb2
VladP
VladP

Reputation: 901

How to do OUTER JOIN and GROUP BY, ORDER BY?

Please, help me with I think simple query I cannot find a solution. Assume I need to use GROUP BY and ORDER BY.. Here is my first table (User profiles where USRID is unique id):

  USRID  USRNAME
   1     usrer 1
   2     usrer 2
   3     usrer 3
   4     usrer 4
   5     usrer 5
   6     usrer 6
   7     usrer 7

Second table keeps user profile update history in no particular order:

  USRID   UPDATEDON
   5    01/01/01 10:00
   1    01/22/03 01:10
   3    02/12/13 04:20
   2    03/30/11 12:30
   2    01/12/13 07:00
   3    01/15/10 09:10
   1    04/08/12 11:20
   4    11/06/07 12:00
   1    01/04/08 11:30
   6    10/02/03 06:00
   7    02/12/07 08:40
   3    01/22/08 02:00
   5    06/12/09 01:50
   5    07/10/13 08:00
   3    08/03/12 10:60
   6    01/04/04 11:00

I need to join two tables to pull latest date user profile was updates to get results like this:

  USRID   UPDATEDON
   1    04/08/12 11:20
   2    01/12/13 07:00
   3    08/03/12 10:60
   4    11/06/07 12:00
   5    07/10/13 08:00
   6    01/04/04 11:00
   7    02/12/07 08:40

Upvotes: 0

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

You don't really need to join the tables for the example data you have provided. You can just aggregate the second one:

select USRID, max(UPDATEDON)
from secondtable
group by USRID
order by USRID;

Upvotes: 4

Related Questions