Solved

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

Posted on 2014-02-18
6
136 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 24

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 24

Expert Comment

by:chaau
ID: 39871125
Row_number does not exist in MySQL.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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 40

Accepted Solution

by:
Sharath earned 500 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now