Skip site navigation (1) Skip section navigation (2)

Re: SELECT DISTINCT ON and ORDER BY

From: "Stanislav Raskin" <sr(at)brainswell(dot)de>
To: "'josep porres'" <jmporres(at)gmail(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: SELECT DISTINCT ON and ORDER BY
Date: 2008-03-28 15:56:25
Message-ID: E1JfGwT-0001eL-00@teena.zerebecki.de (view raw or flat)
Thread:
Lists: pgsql-general
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(at)gmail(dot)com] 
Gesendet: Freitag, 28. März 2008 14:15
An: Stanislav Raskin
Cc: pgsql-general(at)postgresql(dot)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(at)brainswell(dot)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

 

In response to

pgsql-general by date

Next:From: ajcityDate: 2008-03-28 15:59:49
Subject: Re: Need help on how to backup a table
Previous:From: BenDate: 2008-03-28 15:55:00
Subject: Schema design question

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group