asked on

Getting the value with latest date from a joined table, without a subquery

Let's say I have a query like this which gets the latest action from a history table, when there is one (but still lists the user when there isn't a row in the user_hist table):

select u.userid, h.action, h.action_date
from user u
left join user_hist h on h.userid = u.userid
where h.action_date = 
(select max(action_date) from user_hist
  where user_hist.userid = u.userid)
or h.userid is null

Open in new window

How can I rewrite that query so that:
1. I don't need a subquery
2. Preferably, I don't need to put the logic finding the user_hist record with the max date in to the outer most filter (ie the outermost "where"). ie I'm hoping it can be handling in the left join

chaau

You can use LIMIT:

select u.userid, h.action, h.action_date
from user u
left join user_hist h on h.userid = u.userid
ORDER BY h.action_date DESC
LIMIT 1

Open in new window

John_Vidmar

I never heard of Limit, I would use row_number function to order my data:

select	u.userid
,	h.action
,	h.action_date
from	user u
left
join	(	select	userid
		,	action
		,	action_date
		,	rownum = ROW_NUMBER() OVER(ORDER BY userid, action_date DESC)
		from	user_hist
	) h	on	u.userid = h.userid
		and	h.rownum = 1

Open in new window

chaau

Row_number does not exist in MySQL.

Terry Woods

ASKER

Limit isn't what I'm after, at least not in the outmost query, as I want one row per user, not just one row.

ASKER CERTIFIED SOLUTION