SELECT DISTINCT and ORDER BY

[abenstex]

Hi everyone,

i have amongst others the columns pid(varchar) and insertDate(timestamp) and would like to obtain all distinct pid values ordered by insertDate. The problem, though, is that with the following query

select distinct pid, insertDate from mytable order by insertDate

returns all distinct combinations for pid and insertDate. But i want ONLY the distinct pids ordered by the timestamp.

Any advice is really appreciated.

 

[ceco]

try SELECT DISTINCT ON (pid) pid, insertDate ....

see for details

http://www.postgresql.org/docs/8.1/interactive/sql-select.html

 

[abenstex]

I tried that but it seems not to work with order by. That's the error i get when i include the order by:
SELECT DISTINCT ON expressions must match initial ORDER BY expressions

 

[feherke]

Hi

As far as I remember, this was my first question on Tek-Tips in thread699-434666.

But since then, I did not find out the solution. The best workaround is using sub-select :

[b]select[/b] * [b]from[/b] (
  [b]select distinct on[/b] (pid) pid, insertDate [b]from[/b] mytable
) foo [b]order by[/b] insertDate

Feherke.

http://rootshell.be/~feherke/

 

[abenstex]

Thanks that worked!!