selecting rows from table which have distinct values for a column

Question

In my thread based messaging system, the table schema is

> messages table
id(int auto incr primary key)
body(varchar)
time(datetime)

>message_reference table
id(int auto incr primary key)
message_id(forgain key from message table)
sender
receiver

Here, I want to select the first message id which is sent to a new receiver and sender is the user who is logged in.

Doing this with multiple queries and some code is obviously possible but can it be done with a single query for performance issues??

Huh? Can you provide some example data and expected outcome? What constitutes a "new" receiver? How do you know who is logged in? Are you actually seeing performance problems when you use multiple queries? — Tom H, Sep 29 '11 at 11:55
Why do you need two tables? It looks to me like this would be a 1-1 relation so you could put everything in one table. — CyberDude, Sep 29 '11 at 11:55
@cyberdude: Putting it in one table increase redundancy in my db when a user sends a message to multiple receivers. The entire message needs to be inserted again and again. — Sourabh, Sep 29 '11 at 12:01
@tom: New receiver means a receiver id which is not yet selected by the query. The user logged in determined by the code and is queried by the code. Possible outcomes are for ex, I have send 3 messages to tom, so it should return only last sent message not the 1st two. — Sourabh, Sep 29 '11 at 12:03

Jan S · Accepted Answer · 2011-09-30T07:31:04.277

1

You can try

EDIT:

If the id is auto increment, then the id will also increase with time and you can use:

SELECT message_reference.message_id, message_reference.receiver, messages.body
FROM message_reference, messages
WHERE message_reference.message_id IN (SELECT  MIN(message_reference.message_id)
                            FROM message_reference
                            GROUP BY message_reference.receiver)
AND message_reference.message_id = messages.id AND message_reference.sender = <sender>

edited Sep 30 '11 at 07:31

answered Sep 29 '11 at 11:54

Jan S

1,831
15
21

the receiver is not known to us, It should be returned from the query. – Sourabh Sep 29 '11 at 12:45
if the message id is an auto inc field, won't the first msg have the lowest id? can that assumption be made? – Jan S Sep 30 '11 at 04:16
No, message_id is not auto increment, Id in the message table is auto incr. In addition, I don't, you can make a query like SELECT * FROM MESSAGE_REFERENCES WHERE MIN(MESSAGE_ID) AND SENDER= – Sourabh Sep 30 '11 at 04:47
yes, so the id in the message table is an auto inc value and is a foreign key in the message references table. If the messages are stored in the db with the timestamp of exactly when they are created, then logically the earliest message would have the least number, right? – Jan S Sep 30 '11 at 05:04
Yeah I think what you are saying is correct, But I don't know how you are gonna determine the receiver to make that query, Can you please write the query you are thinking about? – Sourabh Sep 30 '11 at 06:55

score 0 · Answer 2 · answered Sep 29 '11 at 12:15

Here's my best guess as to what you want, but it would be easier if you gave known inputs, example data, and expected output.

SELECT
    MR2.message_id
FROM (
    SELECT
        MR.sender,
        MR.receiver,
        M.MIN(`time`) AS min_time
    FROM
        Message_References MR -- Either use plural names (my personal preference) or singular, but don't mix them
    INNER JOIN Messages M ON
        M.id = MR.message_id
    WHERE
        MR.sender = <sender>
    GROUP BY
        MR.received) SQ
INNER JOIN Message_References MR2 ON
    MR2.sender = SQ.sender AND
    MR2.receiver = SQ.receiver AND
    MR2.`time` = SQ.min_time

can you please elaborate the query you have made? I don't understand whats the MR2 table? — Sourabh, Sep 29 '11 at 12:43

score 0 · Answer 3 · answered Sep 29 '11 at 14:32

select mr.message_id from
  message_reference as mr inner join 
    (select mr1.reciever max(m1.time) as time from messages as m1
       inner join message_reference as mr1 on mr1.message_id = m1.id
       group by mr1.reciever) as last
    on mr.reciever = last.reciever and mr.time = last.time

join message reference with "maxtime per reciever" table on reciever and time

score 0 · Answer 4 · answered Sep 30 '11 at 04:55

0

Well I got the answer, Just a group by query worked the way I wanted. I used query

SELECT SENDER,
RECEIVER,
BODY,
TIME,
MESSAGE_ID
FROM MESSAGE_REF JOIN MESSAGE 
ON  MESSAGE.ID=MESSAGE_REF.MESSAGE_ID
ORDER BY 'TIME' GROUP BY RECEIVER`

Thanks everyone for the help.

answered Sep 30 '11 at 04:55

Sourabh

1,757
6
21
43

1

`ORDER BY` clause can come only after the `GROUP BY` clause. Also, even after exchanging, this doesn't produce the correct results always as the GROUP BY clause will select any of the rows which has that field value at random, and that behavior is indeterminate - so sometimes it will give you the result you want and sometimes it might not – Jan S Sep 30 '11 at 05:12
is so? I didn't knew this behavior of group by clause, It is producing the correct result till now, If what you are saying is correct, I will have to find some other way. – Sourabh Sep 30 '11 at 06:57
2

Yes it is, you can take a look at http://stackoverflow.com/questions/1591909/group-by-behavior-when-no-aggregate-functions-are-present-in-the-select-clause – Jan S Sep 30 '11 at 07:27

selecting rows from table which have distinct values for a column

4 Answers4