Re: SELECT DISTINCT ... ORDER BY problem

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-
> owner(at)postgresql(dot)org] On Behalf Of Madison Kelly
> Sent: 08 December 2008 22:19
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] SELECT DISTINCT ... ORDER BY problem
>
> Hi all,
>
> I've got a table that I am trying to SELECT DISTINCT on one column
> and ORDER BY on a second column, but am getting the error:
>
> SELECT DISTINCT ON expressions must match initial ORDER BY expressions
>
> I can't add the second column to the DISTINCT clause because every
> row is unique. Likewise, I can't add the first column to my ORDER BY as
> it'd not sort the way I need it to.
>
> Here is a simplified version of my query:
>
> \d table
> Table "table"
> Column | Type | Modifiers
>
> -----------------+---------+----------------------------------------------
> --
> tbl_id | integer | not null default
> nextval('tbl_seq'::regclass)
> foo | text |
> bar | text |
>
> SELECT DISTINCT ON (foo) foo, bar FROM table WHERE bar < '2008-12-07
> 16:32:46' AND tbl_id=153 ORDER BY bar LIMIT 1;
>

To make the query valid you would have to ORDER BY foo,bar
DISTINCT ON in this case is only going to show the first bar value for each
foo.

Is tbl_id not your PK and only giving 1 row anyway?

>
> I understand from:
>
> http://archives.postgresql.org/pgsql-sql/2007-02/msg00169.php
>
> That this is not really possible because the any given 'foo' column
> could match multiple 'bar' columns, so what do you search by? However,
> it's made some sort of decision as a value is shown in 'bar' for each
> 'foo'.
>
> So my question is two-fold:
>
> 1. Can I not say, somehow, "sort all results by 'bar', and return the
> first/last 'bar' for each distinct 'foo'?
>
> 2. Can I somehow say "Order the results using the value of 'bar' you
> return, regardless of where it came from"?

You can nest queries:

SELECT foo,bar
FROM (SELECT DISTINCT ON (foo) foo,
Bar
FROM table
WHERE bar < '2008-12-07 16:32:46'
AND tbl_id=153 ORDER BY foo,bar
) AS t ORDER BY bar;

Notice that I'm only applying the final order by in the outer query.

David.