如何在sql中使用order by和group by？

Question

Here is my problem: I have these tables:

felado  fogado  fehide  fohide  datum   olvasott    tartalom

felado = sender, fogado = receiver,

I would like to get all where felado or fogado is me and group the lines. So If there are the next lines

sender 1 receiver 2 id 1
sender 1 receiver 2 id 2
sender 1 receiver 2 id 3
sender 2 receiver 1 id 4

I only would like to get one from these lines, the last by ID.

Now I'm trying like this:

SELECT * 
FROM belso_levelezes 
WHERE (felado="'.$_SESSION["userData"]["id"].'"
   or fogado="'.$_SESSION["userData"]["id"].'" )
ORDER BY id DESC 
GROUP BY felado, fogado

It gaves me only one from all, but not the last records. How can I get the last records from each group? I want to get only this:

sender 2 receiver 1 id 4

Answer 1

You could use a subquery to create two columns that are unique per conversation. You can then group on those columns, and grab the maximum id for the conversation. The example assumes your user id is 42:

SELECT  sr1
,       sr2
,       max(id)
FROM    (
        SELECT  case when Sender < Receiver then Sender else Receiver end as sr1
        ,       case when Sender > Receiver then Sender else Receiver end as sr2
        ,       id
        FROM    YourTable
        WHERE   42 in (Sender, Receiver)
        ) as SubQueryAlias
GROUP BY
        sr1
,       sr2