Postgres Pro
Downloads
Home   >   mailing lists

Thread: Sorting with DISTINCT ON

Sorting with DISTINCT ON

From
Nico Grubert
Date:
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

Re: Sorting with DISTINCT ON

From
Jorge Godoy
Date:
Nico Grubert <nicogrubert@gmail.com> writes:

> 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).

It does allow you to sort on both columns.

   SELECT DISTINCT ON (path) path, comment_id, created, title
     FROM bewertungen.tblcomments ORDER BY path, created

Maybe you need a more complex query to get what you want (and use
max(created)...).

--
Jorge Godoy      <jgodoy@gmail.com>

Re: Sorting with DISTINCT ON

From
Nico Grubert
Date:
> It does allow you to sort on both columns.
>
>    SELECT DISTINCT ON (path) path, comment_id, created, title
>      FROM bewertungen.tblcomments ORDER BY path, created

Thank you very much. Works perfect! :-)