Solved

Show only latest row from Join

Posted on 2014-03-12
3
283 Views
Last Modified: 2014-03-29
I've got a query which is to pull a username and passwords from 2 different tables, which works great:-
SELECT `_Users`.`User`, Max(`_Users_Passwords`.`Changed`) AS 'Last Changed', `_Users_Passwords`.`Password`
FROM `_Users` INNER JOIN `_Users_Passwords` ON `_Users`.`User` = `_Users_Passwords`.`User`
GROUP BY `_Users`.`User`, `_Users_Passwords`.`Password`;

Open in new window


However it pulls all the passwords relating to each user, what I want to do is only pull the latest row from `_Users_Passwords`identified my `_Users_Passwords`.`Changed` for each user.

I thought I could use MAX which works if its the only field from `_Users_Passwords` however as soon as I put a second in it pulls all the passwords for each user.

Any ideas how I can get the query to only pull one record from the `_Users_Passwords` table which is the max for the user?

Thank you in advance
0
Comment
Question by:tonelm54
3 Comments
 

Author Comment

by:tonelm54
ID: 39924326
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39924963
I think you might want to change the password-related table to add a column of type TIMESTAMP.  It contains a DATETIME value that will get updated as the row is added or updated.  You can order by this column in DESC and set LIMIT 1 to get the newest password.

You can also use this column to "prune" the table if it grows too big.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 500 total points
ID: 39927309
try this.
SELECT u.*, 
       up.priID, 
       up.dateChanged, 
       up.password 
  FROM users u 
       JOIN (SELECT * 
               FROM (SELECT up.*, 
                            IF(@userID = userID, @rownum := @rownum + 1, @rownum := 1) AS RowNum,
                            IF(@userID <> userID, @userID := userID, 'Dummy')          s 
                       FROM userPasswords up, 
                            (SELECT @rownum := 1, 
                                    @userID := 'Dummy') r 
                      ORDER BY userID, 
                               dateChanged DESC) T1 
              WHERE RowNum = 1) up 
         ON u.userID = up.userID 

Open in new window

http://sqlfiddle.com/#!2/d41a8/17
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

735 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