Re: SELECT DISTINCT ON and ORDER BY

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
>