CODEWITHSUNDEEP
mysql
Ken Vernaillen
Ken Vernaillen

Reputation: 859

mySQL - how to select two distinct min values?

I have a database where I have data that is communicated between two senders. The conversation is grouped by a session id. I want to get the two sender names per session, but I don't seem to find a way without losing sessions.

The first sender always starts with a 'M'. The other sender is a UUID from an android device. (which normally wouldn't start with a capital letter, but is not required)

my first attempt:

select max(sender) as 'first', 
       min(sender) as 'second' 
group by session_id;

Here, in some cases the uuid starts with a number and I get the uuid for both first and second sender.

select min(sender) as 'first', 
       min(sender) as 'second' 
where 'first' <> 'second' 
group by session_id;

Here I lose the sessions where they are the same..

How can I do this?

Example data with min/max attempt = 

'first'             'second'               'session'
M4                   af568906754xxxxx       s1
M4                   af568906754xxxxx       s2
M4                   4da5c573191xxxxx       ...
M5                   c8e953386eaxxxxx
M5                   c8e953386eaxxxxx
M7                   1b92d18d823xxxxx
M7                   1b92d18d823xxxxx
M7                   ac5a231d476xxxxx
M7                   ac5a231d476xxxxx
M7                   c8e953386eaxxxxx
M7                   3266772f89dxxxxx
M962f1a67a9xxxxx     M962f1a67a9xxxxx  (first should be M8)
M95a231d476xxxxx     M95a231d476xxxxx  (first should be M8)
M8                   c16a848a775xxxxx
M8                   c16a848a775xxxxx
M9                   bd3c04eeaf0xxxxx

second attempt will just not show these two..

Upvotes: 0

Views: 52

Answers (2)

juergen d
juergen d

Reputation: 204766

select session_id, 
       group_concat(distinct sender order by case when sender like 'M%' then 1 else 2 end)
from your_table
group by session_id

That query returns a record for each session. For each session it outputs a list of senders. That list is ordered by senders starting with M.

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

SELECT session_id,
       MAX (CASE WHEN sender like 'M%' 
                 THEN sender 
            END) as first,
       MAX (CASE WHEN sender not like 'M%' 
                 THEN sender 
            END) as second
FROM YourTable
GROUP BY session_id

Upvotes: 1

Related Questions