SELECT DISTINCT ... ORDER BY problem - Mailing list pgsql-general

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;


   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"?

Thanks all!

Madi