Search Postgresql Archives

Re: SELECT DISTINCT ON and ORDER BY

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux