Solved

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

Posted on 2014-02-18
6
151 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

756 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