Thread: SELECT DISTINCT ON and ORDER BY
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
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.
select value, max(id) as id, max(order_field) as order_field
from mytable
group by value
order by 3
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
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