使用DISTINCT选择值的SQL查询，最后只发布一个值

Question

I have a problem with selecting only one value posted by one user.

SELECT DISTINCT(zprava.id_prijemce),
    uzivatele.jmeno,
    uzivatele.prijmeni,
    zprava.zobrazeno
FROM zprava
INNER JOIN uzivatele
    ON zprava.id_prijemce = uzivatele.id
WHERE id_uzivatele = $id_uzivatele
ORDER BY datum DESC

This is what I get:

id_prijemce | jmeno  | prijmeni | zobrazeno
31          | Michal | Harych   | 0
31          | Michal | Harych   | 1
35          | Karel  | Pepík    | 0

This is what I need to get:

id_prijemce | jmeno  | prijmeni | zobrazeno
31          | Michal | Harych   | 1
35          | Karel  | Pepík    | 0

I need not to get the row with higher value of "zobrazeno", but the latest posted, so it can be also 0.

Thank you for your advice.

Answer 1

Use a subquery to identify the latest record in the zprava table, the join this back to zprava and to uzivatele to obtain the results you want.

SELECT
    t1.id_prijemce,
    t3.jmeno,
    t3.prijmeni,
    t1.zobrazeno
FROM zprava t1
INNER JOIN
(
    SELECT id_prijemce, MAX(datum) AS max_datum
    FROM zprava
    GROUP BY id_prijemce
) t2
    ON t1.id_prijemce = t2.id_prijemce AND
       t1.datum       = t2.max_datum
INNER JOIN uzivatele t3
    ON t1.id_prijemce = t3.id
WHERE t1.id_uzivatele = $id_uzivatele
ORDER BY t1.datum DESC