CODEWITHSUNDEEP
mysqlsqlgroup-by
Amrata Ramchandani
Amrata Ramchandani

Reputation: 93

How to get max(column value) based on another column value

I have a table with following schema and sample values.

enter image description here

I am trying to write a query which will give me for each user its max(timestamp) for connected status as well as disconnected status.

Example of the output schema for the required query I am looking for is:

Username | Connected_MAX_Timestamp | Disconnected_MAX_Timestamp

The query which I tried is this :

SELECT username,socketstatus,max(timestamp) from socketinfo group by username,socketstatus

and the output for the same is :

enter image description here

Can someone tell me how to achieve the required output ?

Upvotes: 0

Views: 758

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

Just use conditional aggregation:

select username,
       max(case when socketstatus = 'Connected' then timestamp end) as max_connected_timestamp,
       max(case when socketstatus = 'Disconnected' then timestamp end) as max_disconnected_timestamp,
from socketinfo
group by username;

Upvotes: 2

Heiko Jakubzik
Heiko Jakubzik

Reputation: 419

SELECT t1.username,
       t1.socketstatus,
       max(t1.timestamp) AS "Connected_MAX_Timestamp",
       t2.username,
       t2.socketstatus,
       max(t2.timestamp) "Disconnected_MAX_Timestamp"
FROM socketinfo t1,
     socketinfo t2
WHERE t1.socketstatus='Connected'
  AND t2.socketstatus='Disconnected'
  AND t1.username=t2.username
GROUP BY t1.username,
         t1.socketstatus,
         t2.username,
         t2.socketstatus;

This works on Postgres 9.5, if I understood your question correctly.

Upvotes: 1

Related Questions