Sorting with DISTINCT ON

From: Nico Grubert <nicogrubert(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Sorting with DISTINCT ON
Date: 2007-01-08 13:34:26
Message-ID: 45A24862.3080902@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ravi Malghan 2007-01-08 13:36:25 Web interface to update/change postgres data.
Previous Message Rafal Pietrak 2007-01-08 12:39:58 TRIGGER BEFORE INSERT