Skip site navigation (1) Skip section navigation (2)

Re: SELECT DISTINCT ON and ORDER BY

From: "josep porres" <jmporres(at)gmail(dot)com>
To: "Stanislav Raskin" <sr(at)brainswell(dot)de>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: SELECT DISTINCT ON and ORDER BY
Date: 2008-03-28 13:15:06
Message-ID: d2d532610803280615k45ca35f2m2b96184ade806780@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
maybe this?

select value, max(id) as id, max(order_field) as order_field
from mytable
group by value
order by 3



2008/3/28, Stanislav Raskin <sr(at)brainswell(dot)de>:
>
>  Hello everybody,
>
>
>
> I have a table like this one:
>
>
>
> id         value     order_field
>
> 1          10        3
>
> 2          12        4
>
> 3          10        1
>
> 4          5          8
>
> 5          12        2
>
>
>
> What I want to do, is to do something like
>
>
>
> SLECT DISTINCT ON (my_table.value)
>
> my_table.id, my_table.value, my_table.order_field
>
> FROM my_table
>
> ORDER BY order_field
>
>
>
> 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?
>
>
>
> Thank you in advance
>

In response to

Responses

pgsql-general by date

Next:From: Richard HuxtonDate: 2008-03-28 13:21:05
Subject: Re: Delete after trigger fixing the key of row numbers
Previous:From: Volkan YAZICIDate: 2008-03-28 13:13:23
Subject: Re: SELECT DISTINCT ON and ORDER BY

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group