Hi there,
I have a problem sorting a SQL result if I use DISTINCT ON.
I have a table "tblcomment" with these columns:
id (serial)
path (varchar)
created (timestamp)
title (varchar)
These records are in the table "tblcomment":
id path created title
------------------------------------------------------------
11 /var/black 2007-01-07 22:17:03.001837 Any title
17 /var/blue 2007-01-07 20:35:55.289713 Any title
13 /var/blue 2007-01-15 15:52:58.438375 Any title
12 /var/green 2007-01-08 19:03:50.470073 Any title
18 /var/red 2007-01-07 08:41:47.152676 Any title
Now, I want to get all results from this table and if there are
duplicates, I want the row whose "created" column has the latest date.
In this example, I want to have this result:
id path created title
------------------------------------------------------------
11 /var/black 2007-01-07 22:17:03.001837 Any title
13 /var/blue 2007-01-15 15:52:58.438375 Any title
12 /var/green 2007-01-08 19:03:50.470073 Any title
18 /var/red 2007-01-07 08:41:47.152676 Any title
My first try was this SQL query:
SELECT DISTINCT ON (path) path, comment_id, created, title
FROM bewertungen.tblcomments
This does not allow me to append "ORDER BY created" since I can only
sort on path because of DISTINCT ON (path).
My second try was a sub query like this:
SELECT comment_id, path, created, title
FROM
( SELECT DISTINCT ON (path) path, comment_id, created, title
FROM bewertungen.tblcomments
) foo_alias
ORDER BY created DESC
But this results into:
id path created title
------------------------------------------------------------
11 /var/black 2007-01-07 22:17:03.001837 Any title
17 /var/blue 2007-01-07 20:35:55.289713 Any title
12 /var/green 2007-01-08 19:03:50.470073 Any title
18 /var/red 2007-01-07 08:41:47.152676 Any title
No matter, if I user ORDER BY created DESC or
ORDER BY created ASC. It seems that postgres always takes the first row
of the duplicates. In this example:
17 /var/blue 2007-01-07 20:35:55.289713 Any title.
Any idea, how I can solve my problem?
Regards,
Nico