CODEWITHSUNDEEP
mysql
user3130014
user3130014

Reputation: 11

MYSQL select from table with duplicate values

I am using MYSQL database.
I have a table with four columns id, sender, reciever and recieverread; reciever and sender columns can contain duplicate values is there a way to select only the last row with the duplicate value?

I tried
SELECT DISTINCT sender FROM msg WHERE sender = sender

Upvotes: 0

Views: 115

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

A standard way to approach this in MySQL is with a not exists clause:

select m.*
from msg m
where not exists (select 1 from msg m2 where m2.sender = m.sender and m2.id > m.id);

That is, select the row for a given sender that has no larger ids in the table.

Upvotes: 0

echo_Me
echo_Me

Reputation: 37233

try this

select maxid ,sender from msg where id in (
                       select max(id) as maxid from msg GROUP BY sender)

Upvotes: 0

neelsg
neelsg

Reputation: 4842

How about:

SELECT MAX(id) AS id_last, sender, receiver, recieverread FROM msg GROUP BY sender, receiver, recieverread

Upvotes: 0

BlueDeath
BlueDeath

Reputation: 134

You can try something like this:

SELECT sender FROM msg GROUP BY sender HAVING COUNT(sender) > 1;

Upvotes: 1

SagarPPanchal
SagarPPanchal

Reputation: 10111

This will prevent to data duplication. You may try this, 

SELECT DISTINCT sender FROM msg WHERE sender = sender group by sender

Upvotes: 0

Related Questions