CODEWITHSUNDEEP
mysql
Hardik Gondalia
Hardik Gondalia

Reputation: 3717

Distinct value for multiple values in mysql

I want to find distinct value based on 3 columns. Here the Query which I am trying

select * from messages where (message_from=4 or message_to=4) group by message_from,message_to,bidpost_id
ORDER BY `messages`.`message_id` ASC

which gives me following output:
enter image description here

but I don't want message id 5 because it has already comes before as message_from and message_to is already comes in message_id = 4

I want distinct value on base of bidpost_id, message_from and message_to

Note: message_from and message_to pair must be unique

My desirable output is:
enter image description here

Upvotes: 1

Views: 29

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can do this with a join or exists. For example:

select m.*
from messages m
where not exists (select 1
                  from messages m2
                  where m2.bidpost_id = m.bidpost_id and
                        m2.message_from in (m.message_to, m.message_from) and
                        m2.message_to in (m.message_to, m.message_from) and
                        m2.message_id < m.message_id
                 );

This returns the first message (based on message_id) for those three columns.

Another method that doesn't use explicit join is:

select m.*
from messages m
where m.message_id in (select min(m.message_id)
                       from messages m
                       group by bidpost_id,
                                least(message_from, message_to),
                                greatest(message_from, message_to)
                      );

Performance might not be as good for this, even with the recommended index on messages(bidpost_id, message_from, message_to, message_id).

Upvotes: 3

Hardik Gondalia
Hardik Gondalia

Reputation: 3717

I done with this solution:

select message_id, value1, value2, bidpost_id from (select message_id, `bidpost_id`, 
least(`message_from`, `message_to`) as value1
, greatest(`message_from`, `message_to`) as value2
from `messages` group by value1, value2, bidpost_id order by message_id) as t where value1= 4 or value2 =4

Thanks to Gordon Linoff, I took some code from your answer from another post

Upvotes: 0

Related Questions