Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-02-18
6
Medium Priority
?
162 Views
Last Modified: 2015-05-06
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
Comment
Question by:Terry Woods
6 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39869514
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
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39870248
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
 
LVL 25

Expert Comment

by:chaau
ID: 39871125
Row_number does not exist in MySQL.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 35

Author Comment

by:Terry Woods
ID: 39871443
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
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 39927496
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
 
LVL 35

Assisted Solution

by:Terry Woods
Terry Woods earned 0 total points
ID: 39938775
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month8 days, 4 hours left to enroll

824 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question