SELECT DISTINCT ON and ORDER BY

On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote:

id value order_field
1 10 3
2 12 4
3 10 1
4 5 8
5 12 2

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?

ORDER BY's in conjunction with DISTINCT ON are used to specify which
values you want for the other expressions in your query. For example
for value 10, do you want id to be 1 or 2, and should the order be from
the same row, or something else.

You're additionally wanting to order by the "order" column, which you
need to express as another step, i.e. a subselect something like:

SELECT id, value
FROM (
SELECT DISTINCT ON (value) id, value, order
FROM table
ORDER BY value, id) x
ORDER BY order;

No programming language will ever do exactly what you want straight
away, it's a matter of using the tools it gives you.

Sam

On Fri, 28 Mar 2008, Sam Mason <sam@samason.me.uk> writes:

On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote:

The result in this case should be:

id value order_field
3 10 1
5 12 2
4 5 8

SELECT id, value
FROM (
SELECT DISTINCT ON (value) id, value, order
FROM table
ORDER BY value, id) x
ORDER BY order;

returns

id | value
----+-------
1 | 10
2 | 12
4 | 5

to get the right results, append a DESC after "id" column in ORDER
BY:

id | value
----+-------
3 | 10
5 | 12
4 | 5

BTW, if I'm not mistaken, this solution assumes an order relation
between your "id" and "value" columns.

Regards.

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>:

On Fri, 28 Mar 2008, Sam Mason <sam@samason.me.uk> writes:

On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote:

The result in this case should be:

id value order_field
3 10 1
5 12 2
4 5 8

Yet another lame solution:

test=# SELECT max(id) AS id, min(value) AS value, min(weight) AS weight
FROM tmp
GROUP BY value
ORDER BY min(weight);
id | value | weight
----+-------+--------
3 | 10 | 1
5 | 12 | 2
4 | 5 | 8
(3 rows)

Regards.

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

josep porres escreveu:

maybe this?

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

Wrong. For the op data you will obtain tuples not in original relation.

bdteste=# SELECT * FROM foo;
id | value | order_field
----+-------+-------------
1 | 10 | 3
2 | 12 | 4
3 | 10 | 1
4 | 5 | 8
5 | 12 | 2
(5 registros)

bdteste=# SELECT max(id), value, max(order_field)
FROM foo GROUP BY value
ORDER BY 3;
max | value | max
-----+-------+-----
3 | 10 | 3
5 | 12 | 4
4 | 5 | 8
(3 registros)

Try:
bdteste=# SELECT * FROM (
SELECT DISTINCT ON (value) id, value, order_field
FROM foo ORDER BY value, order_field) AS bar
ORDER BY order_field;
id | value | order_field
----+-------+-------------
3 | 10 | 1
5 | 12 | 2
4 | 5 | 8
(3 registros)

Osvaldo