由mysql排序和分组不工作[关闭]

Question

oke this is wat i want

Database

id | fromm | ontvanger | date | msg | gelezen
--------------------------------------------------
1  | kees  | marc     | time_stamp  | 0
1  | hans  | marc     | time_stamp  | 1
3  | kees  | marc     | time_stamp  | 0
5  | selma | marc     | time_stamp  | 1
6  | kees  | marc     | time_stamp  | 0
7  | kees  | marc     | time_stamp  | 0

this is wat i want GROUP BY fromm

1  | hans  | marc     | time_stamp  | 1
3  | kees  | marc     | time_stamp  | 0
5  | selma | marc     | time_stamp  | 1

$result = mysql_query("SELECT * FROM berichten where ontvanger = '$session->username' 
GROUP BY fromm ORDER BY date DESC
");

Show the latest record and gelezen is 0 first

Answer 1

SELECT  a.*
FROM    berichten  a
        INNER JOIN
        (
            SELECT  fromm, MAX(date) date
            FROM    berichten
            GROUP   BY fromm
        ) b ON  a.fromm = b.fromm AND
                a.date = b.date
WHERE   a.ontvanger = '$session->username'
ORDER   BY (gelezen = 0) DESC, id

The subquery lets you get the latest date for each fromm. The result of it is then joined back on the original table to get the other values of the columns.

(gelezen = 0) results in a boolean value that if it is true will give 1 otherwise 0. Since you want to order the row based on gelezen = 0, apply a descending order on the result of boolean arithmetic.