CODEWITHSUNDEEP
mysqlsql
Shlomo
Shlomo

Reputation: 3990

Select only 1 row when multiple max values

I want to select the max value in a column A. Now, I have multiple max values in column A. How can I just take 1 row with the max value and strip the rest off? I tried using distinct and limit 1 without success.

Expected: Only 2 rows, 1 has message "srth" and the other has either "the-same-ts-1" or "the-same-ts-2" but not both, since they have the same max ts 1413588888

My query: 

SELECT A.*, DU.user as username, DU.thumbnail 
            FROM DB_CHATS A
            INNER JOIN (
               SELECT distinct max(timestamp) TS, user, partner
               FROM db_chats
               GROUP BY  user,partner ) T
              on A.TimeStamp=T.TS 
             and A.user=T.User
            LEFT JOIN  db_users DU on t.partner = DU.id
where A.user = 'utQ8YDxD6kSrlI5QtFOUAE4h'

Fiddle: sqlfiddle

Upvotes: 0

Views: 168

Answers (1)

FuzzyTree
FuzzyTree

Reputation: 32392

If you don't care which row having the max time stamp is returned, you can add group by user, partner, timestamp to the end of your query.

SELECT A.*, DU.user as username, DU.thumbnail 
            FROM DB_CHATS A
            INNER JOIN (
               SELECT distinct max(timestamp) TS, user, partner
               FROM db_chats
               GROUP BY  user,partner ) T
              on A.TimeStamp=T.TS 
             and A.user=T.User
            LEFT JOIN  db_users DU on t.partner = DU.id
where A.user = 'utQ8YDxD6kSrlI5QtFOUAE4h'
group by user, partner, timestamp

http://sqlfiddle.com/#!2/efe6e/2

Upvotes: 1

Related Questions