Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 295
  • Last Modified:

Show only latest row from Join

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
tonelm54
Asked:
tonelm54
1 Solution
 
tonelm54Author Commented:
0
 
Ray PaseurCommented:
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
 
SharathData EngineerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now