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

Show only latest row from Join

Posted on 2014-03-12
3
282 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 109

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 40

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

860 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