• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

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
0
Terry Woods
Asked:
Terry Woods
2 Solutions
 
chaauCommented:
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

0
 
John_VidmarCommented:
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

0
 
chaauCommented:
Row_number does not exist in MySQL.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Terry WoodsIT GuruAuthor Commented:
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.
0
 
SharathData EngineerCommented:
You need to implement ROWNUMBER to get the max action_date. check this.
SELECT u.userid, 
       uh.action, 
       uh.action_date 
  FROM users u 
       LEFT JOIN (SELECT * 
                    FROM (SELECT h.*, 
                                 IF(@userID = userID, @rownum := @rownum + 1, @rownum := 1) AS RowNum,
                                 IF(@userID <> userID, @userID := userID, 'Dummy')          s
                            FROM user_hist h, 
                                 (SELECT @rownum := 1, 
                                         @userID := 'Dummy') r 
                           ORDER BY userID, 
                                    action_date DESC) T1 
                   WHERE RowNum = 1) uh 
              ON u.userID = uh.userID 

Open in new window

0
 
Terry WoodsIT GuruAuthor Commented:
Thanks all for your suggestions, but I think my query already returns the max action_date in a much simpler way!

I was hoping there might be an even simpler way to do it, but it appears not.
0

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now