Yes, it works fine. Never came to my mind to simply use aggregate functions on fields which I do not want in the group clause.
Is it common practice to do so in such cases? It seems odd somehow.
Von: josep porres [mailto:jmporres@gmail.com]
Gesendet: Freitag, 28. März 2008 14:15
An: Stanislav Raskin
Cc: pgsql-general@postgresql.org
Betreff: Re: [GENERAL] 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@brainswell.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