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:[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]>: 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
