CODEWITHSUNDEEP
mysqlsql
TRicks
TRicks

Reputation: 35

Retrieve MAX timestamp from query

I'm have a problem with my MySQL query, currently I have the following

SELECT DISTINCT 
  members.userid,
  messaging.toName,
  members.url,
  messaging.timestamp AS utime 
FROM
  messaging 
  JOIN members 
    ON members.userid = messaging.senderid 
WHERE (
    messaging.userid = '$profileid' 
    OR messaging.senderid = '1000450'
  ) 
ORDER BY messaging.timestamp DESC

Which brings back

userid
toName
url
timestamp

The above query brings back all the different timestamp values but what i need it to do is just bring back the latest timestamp.

for example 

userid  | toName | url | timestamp       |
------------------------------------------
Test    |  Dave  | url | 7787263803      |
Test    |  Dave  | url | 7787263804      |
Test    |  Dave  | url | 7787263805      |
Test    |  Dave  | url | 7787263806      |

How can I edit the query so it will bring back just the maxium timestamp like below?

userid  | toName | url | timestamp       |
------------------------------------------
Test    |  Dave  | url | 7787263806      |

Any help would be great

Upvotes: 0

Views: 71

Answers (3)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17147

One way to do that would be (using your query) to limit the output to 1 row, since you've already descending ordered your resultset by column timestamp.

SELECT DISTINCT 
  members.userid,
  messaging.toName,
  members.url,
  messaging.timestamp AS utime 
FROM
  messaging 
  JOIN members 
    ON members.userid = messaging.senderid 
WHERE (
    messaging.userid = '$profileid' 
    OR messaging.senderid = '1000450'
  ) 
ORDER BY messaging.timestamp DESC 
LIMIT 1

Another way of doing this would be to use aggregate function MAX()

SELECT DISTINCT 
  members.userid,
  messaging.toName,
  members.url,
  MAX(messaging.timestamp) AS utime 
FROM
  messaging 
  JOIN members 
    ON members.userid = messaging.senderid 
WHERE (
    messaging.userid = '$profileid' 
    OR messaging.senderid = '1000450'
  ) 
GROUP BY 1,2,3
--  ORDER BY messaging.timestamp DESC # Unnecessary sorting in this solution

Which will produce a maximum timestamp for each distinct pair of (userid, toName, url).

Also, you may get the same result using a WINDOW function:

SELECT 
    *
FROM (
    SELECT DISTINCT 
      members.userid,
      messaging.toName,
      members.url,
      messaging.timestamp AS utime,
      rank() OVER (ORDER BY messaging.timestamp) AS position 
    FROM
      messaging 
      JOIN members 
        ON members.userid = messaging.senderid 
    WHERE (
        messaging.userid = '$profileid' 
        OR messaging.senderid = '1000450'
      ) 
 -- ORDER BY messaging.timestamp DESC # Unnecessary sorting in this solution
) WHERE position = 1

Big advantage of this solution is that changing the WHERE clause you can get second, third ... very quickly.

Upvotes: 0

Xordal
Xordal

Reputation: 1369

For return 4 columns with max timestamp, u should use group by operator and max for target field

SELECT DISTINCT 
  members.userid,
  messaging.toName,
  members.url,
  max(messaging.timestamp) AS utime 
FROM
  messaging 
  JOIN members 
    ON members.userid = messaging.senderid 
WHERE (
    messaging.userid = '$profileid' 
    OR messaging.senderid = '1000450'
  ) 
group by members.userid,
      messaging.toName,
      members.url

or u can use limit 1 with order by desc:

SELECT DISTINCT 
  members.userid,
  messaging.toName,
  members.url,
  max(messaging.timestamp) AS utime 
FROM
  messaging 
  JOIN members 
    ON members.userid = messaging.senderid 
WHERE (
    messaging.userid = '$profileid' 
    OR messaging.senderid = '1000450'
  )
ORDER BY messaging.timestamp DESC limit 1

Upvotes: 1

rvandoni
rvandoni

Reputation: 3407

just add LIMIT 1 at the end of your query

Upvotes: 1

Related Questions