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
Solved

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

Posted on 2014-02-18
6
150 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 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