Re: SELECT DISTINCT ON and ORDER BY

From: Sam Mason <sam(at)samason(dot)me(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: SELECT DISTINCT ON and ORDER BY
Date: 2008-03-28 12:59:09
Message-ID: 20080328125909.GY6870@frubble.xen.chris-lamb.co.uk
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote:
> id value order_field
> 1 10 3
> 2 12 4
> 3 10 1
> 4 5 8
> 5 12 2
>
> Hence selecting rows with distinct values, but primarily ordered by
> order_field, instead of value, which is requires by DISTINCT ON.
>
> The result in this case should be:
>
> id value order_field
> 3 10 1
> 5 12 2
> 4 5 8
>
> How do I do this? I do need order_field in the select list to use it in the
> ORDER statement, which is why - as far as I can see - GROUP BY and SELECT
> DISTINCT are useless. Did I miss out on something?

ORDER BY's in conjunction with DISTINCT ON are used to specify which
values you want for the other expressions in your query. For example
for value 10, do you want id to be 1 or 2, and should the order be from
the same row, or something else.

You're additionally wanting to order by the "order" column, which you
need to express as another step, i.e. a subselect something like:

SELECT id, value
FROM (
SELECT DISTINCT ON (value) id, value, order
FROM table
ORDER BY value, id) x
ORDER BY order;

No programming language will ever do exactly what you want straight
away, it's a matter of using the tools it gives you.

Sam

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Volkan YAZICI 2008-03-28 13:13:23 Re: SELECT DISTINCT ON and ORDER BY
Previous Message Teemu Juntunen, e-ngine 2008-03-28 12:47:05 Delete after trigger fixing the key of row numbers