Ordering multiple columns

Question

I have a table msg(ID,TO,FROM,MSG,TIME) and I need to select distinct tuples from TO and FROM and order the result based on the latest time a message has been sent. The queries I have used so for

SELECT DISTINCT value 
FROM ( SELECT msg.to AS value, time 
        FROM msg 
        WHERE msg.from = '".$_SESSION["username"]."' 
    UNION 
        SELECT msg.from AS value, time 
        FROM msg 
        WHERE msg.to = '".$_SESSION["username"]."') TT 
order by time desc

And

select t1.to, t1.time 
from msg t1 
where t1.from='".$_SESSION["username"]."' 
    and t1.time=(
        select max(time) 
        from msg t2 
        where t2.to=t1.to) 
union 
select t1.from, t1.time 
from msg t1 
where t1.to='".$_SESSION["username"]."' 
    and t1.time=(
        select max(time) 
        from msg t2 
        where t2.from=t1.from)

But none give the desried result.

If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Jun 05 '15 at 20:37
See http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column?rq=1 for how to get the row with the last time in each group. Then just order the result by the time. — Barmar, Jun 05 '15 at 20:48

score 0 · Answer 1 · answered Jun 05 '15 at 21:31

0

What is the "desired result"? How is it different from the following?

select TO, FROM, max(TIME) as TIME
from msg
group by TO, FROM
order by max(TIME) desc

answered Jun 05 '15 at 21:31

James K. Lowden

7,574
1
16
31

score 0 · Accepted Answer · answered Jun 06 '15 at 04:23

SELECT value, max(time) as latest_time_sent
FROM ( SELECT msg.to AS value, time 
        FROM msg 
        WHERE msg.from = '".$_SESSION["username"]."' 
    UNION ALL
        SELECT msg.from AS value, time 
        FROM msg 
        WHERE msg.to = '".$_SESSION["username"]."') TT 
group by value
order by latest_time_sent desc

Ordering multiple columns

2 Answers2