CODEWITHSUNDEEP
mysqlsqlsql-order-by
steve
steve

Reputation: 2519

Ordering oddity with sub queries

I have the SQL:

select
sub.id,
corr
from 
    (
        select
            m.id,
            m.sent_time,
            (case when m.to_user = '58' then m.from_user else m.to_user end) as corr
        from
            messages m
        where
            (
                from_user = '58' OR
                to_user = '58'
            )
        order by
            m.sent_time desc
    ) as sub
group by
    sub.corr
order by
    sent_time DESC

In a site's source code I have inherited. The SQL retrieves the message ID and the user ID of the other party communicated with where messages have been sent to or from the logged in user (in this case user ID 58.

The query at the moment always returns the oldest message ID between the user (58) and the the other user. Changing the order by to use sent_time DESC has not changed this.

I am probably missing something obvious - but how can I get the message ID of the most recent message rather than the oldest by changing this query?

Upvotes: 0

Views: 22

Answers (2)

Nick
Nick

Reputation: 147216

Your query doesn't give the results you want because as soon as you use GROUP BY it selects random values for all unaggregated columns. I think you can simplify your query to avoid this problem:

SELECT id, 
    CASE WHEN to_user = '58' THEN from_user ELSE to_user END AS corr
FROM messages m
WHERE (to_user = 58 OR from_user = 58) AND
    sent_time = (SELECT MAX(sent_time) 
                 FROM messages m1 
                 WHERE m1.to_user = 58 OR m1.from_user = 58)

Upvotes: 1

yusher
yusher

Reputation: 226

If I understand what you mean, you can try following sql:

select
max(sub.id),
sub.corr
from 
    (
        select
            m.id,
            m.sent_time,
            (case when m.to_user = '58' then m.from_user else m.to_user end) as corr
        from
            messages m
        where
            (
                from_user = '58' OR
                to_user = '58'
            )
        order by
            m.sent_time desc
    ) as sub
group by
    sub.corr

Upvotes: 0

Related Questions