SELECT DISTINCT ON and ORDER BY

From: "Stanislav Raskin" <sr(at)brainswell(dot)de>
To: <pgsql-general(at)postgresql(dot)org>
Subject: SELECT DISTINCT ON and ORDER BY
Date: 2008-03-28 12:12:49
Message-ID: E1JfDS1-0007Nv-00@teena.zerebecki.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Teemu Juntunen 2008-03-28 12:27:59 Delete after trigger fixing the key of row numbers
Previous Message purple_cat 2008-03-28 11:35:07 PostgreSQL terminates after crash of another server process