Take the 2-minute tour ×

I want to get only the rows with a unique value for a certain field (2 actually). My table is like this:

id    senderID    receiverID    ...    ...    ...
0     0           1             ...    ...    ...
1     2           1             ...    ...    ...
2     1           0             ...    ...    ...
3     0           2             ...    ...    ...
4     2           0             ...    ...    ...
5     1           2             ...    ...    ...

In this table, id is unique always. senderID is the ID of the user who sent the message, receiverID is the ID of the user who received the message. ... are some fields (like text) that don't really matter and they aren't unique either.

So first of all, I want to get all rows where I (#1) am the sender or the receiver.

SQL: "SELECT id FROM table WHERE senderID='1' OR receiverID='1'";

This will return (0, 1, 2, 5).

But now, I only want all unique records. So the conditions are:

  1. 1 should be the senderID or the receiverID.
  2. if ((senderID == '1' && "__ receiverID is not yet listed__") || (receiverID == '1' && "__ senderID is not yet listed__")) in pseudo-code.

The final return should be (0, 1).

How do I do this in (My)SQL? I do know how to do this using PHP, but when there're thousands of records it's not fast enough anymore.

Kind regards,


share|improve this question
So you want results of two queries with one ? If true you have to sort results with PHP. –  MatTheCat Oct 10 '10 at 12:51

3 Answers 3

up vote 1 down vote accepted
select min(id) from 
  select id, senderID pID from table where receiverID = '1'
  select id, receiverID pID from table where senderID = '1'
) as fred
group by pID;

For your data set, this gives:

| min(id) |
|       0 |
|       1 |
share|improve this answer
Wow, thanks Martin! This works soooo great for me. It does exactly what I want it does and I don't need to use multiple mysql_query-calls. Thank you very very much! –  Tim Oct 11 '10 at 8:46

If you do this, you will get all distinct ID's where you are sender and the distinct id's where you are receiver.

Additionally, the UNION will combine both the results.

In your logic, you can use the 2nd column value ('S'/ 'R') to filter separate the 2 sets if you need to do things separately for sent / received id's.

SELECT distinct id, 'S' as Type FROM table WHERE senderID='1' 
SELECT distinct id, 'R' as Type FROM table WHERE receiverID='1' 
share|improve this answer
Hi InSane, thank you very much. This also comes very close. I did need to remove the id-field from DISTINCT. After that I get: {receiverID='someReceiverID', receiverID='someReceiverID', receiverID='someSenderID'}. Then I use a second SQL: "SELECT id FROM table WHERE (senderID='1' AND receiverID='$receiverID') OR (senderID='$receiverID' AND receiverID='1') ORDER BY date DESC LIMIT 0, 1" to get the ID of the latest message in the thread. –  Tim Oct 11 '10 at 8:59

Something like

SELECT DISTINCT id, senderID, receiverID FROM table WHERE senderID='1' OR receiverID='1';


DISTINCT keyword will remove any duplicates from the result. Works good so far, you don't add any other columns other than id, senderID and receiverID.

Otherwise you could also use the GROUP BY clause

SELECT id FROM table WHERE senderID='1' OR receiverID='1' GROUP BY senderID, receiverID;
share|improve this answer
Hi Tseng, thank you for your answers! The first (DISTINCT) doesn't filter at all since the 'id'-field is unique for every row. The second (GROUP BY) is very close, but still returns the ID of every row (however, ordered by thread/conversation). However, by combining your answer with InSane's I got it working! –  Tim Oct 11 '10 at 8:46

Your Answer


