Sortowanie względem pola nie będącego w DISTINCT ON

Problem:
Chcemy wyświetlić jedynie unikalne rekordy względem konkretnego pola/pól i posortować innym polu. W naszym przykładzie chcemy otrzymać zestawienie id_user – ostatnie logowanie

Posiadane dane:
Tabela z datami logowań użytkowników aplikacji w tabeli postaci:

CREATE TABLE logs(

	id SERIAL PRIMARY KEY, -- unikalny klucz główny

	id_user INTEGER, -- klucz obcy tabeli użytkowników

	date TIMESTAMP -- data logowania

);

Przykładowe rekordy:

INSERT INTO logs(id_user,date) VALUES(1,'2008-01-01');

INSERT INTO logs(id_user,date) VALUES(1,'2008-01-01');

INSERT INTO logs(id_user,date) VALUES(4,'2008-01-01');

INSERT INTO logs(id_user,date) VALUES(2,'2008-01-01');

INSERT INTO logs(id_user,date) VALUES(3,'2008-04-01');

INSERT INTO logs(id_user,date) VALUES(7,'2008-04-01');

INSERT INTO logs(id_user,date) VALUES(1,'2008-05-01');

INSERT INTO logs(id_user,date) VALUES(4,'2008-05-01');

INSERT INTO logs(id_user,date) VALUES(5,'2008-06-01');

INSERT INTO logs(id_user,date) VALUES(5,'2008-08-01');

INSERT INTO logs(id_user,date) VALUES(7,'2008-10-01');

Błędne zapytanie nr 1:
W pierwszym momencie wielu początkujących programistów pomyśli o zapytaniu:

SELECT

 DISTINCT ON (id_user)

 *

FROM

 logs

ORDER BY

 date DESC;

Niestety nie jest to poprawne zapytanie, postgres wyrzuci błąd:

ERROR:  SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Oznacza to, że pola, które zawiera klauzula ORDER BY muszą się znaleźć na początku klauzuli DISTINCT ON.
W tym przypadku poprawne zapytanie musiało by zawierać DISTINCT ON (date, id_user).
Niestety wtedy nie dostaniemy pożądanego rezultatu (unikalność po kolumnie id_user) gdyż badana będzie
unikalność względem dwóch pól.

Błędne zapytanie nr 2:

SELECT

 *

FROM

 (SELECT

 	DISTINCT ON (id_user)

 	*

 FROM

 	logs

) AS sub

ORDER BY

 sub.date DESC;

Idąc tropem unikalności można użyć podzapytania, które zwraca unikalne względem id_user rekordy a dopiero później, zwrócone przez nie rekordy posortować. Powyższe zapytanie wykona się i zwróci pewne rezultaty.

id id_user date
11 7 2008-10-01 00:00:00
9 5 2008-06-01 00:00:00
8 4 2008-05-01 00:00:00
5 3 2008-04-01 00:00:00
2 1 2008-01-01 00:00:00
4 2 2008-01-01 00:00:00

Niestety nie będą one do końca poprawne gdyż podzapytanie DISTINCT pozostawia pierwszy napotkany rekord zawierający unikalną wartość id_user. Zatem jeżeli dane nie są posortowane względem id_user i daty chronologicznie to uzyskamy wyniki nieprawidłowe. W naszym przypadku id_user=1 została zwrócona data=2008-01-01 gdyż jest ona bliżej początku niż rekord z datą=2008-05-01.

Rozwiązanie:

SELECT

 *

FROM

 (SELECT

 	DISTINCT ON (sub2.id_user)

 	sub2.*

 FROM

 	(SELECT

 		*

 	FROM

 		logs

 	ORDER BY

 		id_user ASC,

 		date DESC

 	) AS sub2

) AS sub

ORDER BY

 sub.date DESC;

W podzapytaniu sub2 sortujemy dane po id_user i date malejąco tak aby każdy id_user na czele miał rekord z datą najpóźniejszą. Takie dane przekazujemy zapytaniu wcześniejszemu, które najpierw wyciąga rekordy unikalne względem kolumny id_user a następnie tak ograniczony zbiór sortuje względem kolumny date. Jako że dostarczyliśmy dane posortowane otrzymane rezultaty są zgodne z prawdą:

id id_user date
11 7 2008-10-01 00:00:00
10 5 2008-08-01 00:00:00
7 1 2008-05-01 00:00:00
8 4 2008-05-01 00:00:00
5 3 2008-04-01 00:00:00
4 2 2008-01-01 00:00:00