SELECT DISTINCT ... ORDER BY problem

From: Madison Kelly <linux(at)alteeve(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: SELECT DISTINCT ... ORDER BY problem
Date: 2008-12-08 22:19:01
Message-ID: 493D9D55.6080904@alteeve.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David Rowley 2008-12-08 23:16:29 Re: SELECT DISTINCT ... ORDER BY problem
Previous Message aravind chandu 2008-12-08 22:17:02 Problem Related to storing the field value in a String