On Mon, Dec 08, 2008 at 11:16:29PM -0000, David Rowley wrote: > > -----Original Message----- > > From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general- > > owner@xxxxxxxxxxxxxx] On Behalf Of Madison Kelly > > Sent: 08 December 2008 22:19 > > To: pgsql-general@xxxxxxxxxxxxxx > > Subject: 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. When we get windowing functions, a lot of this pain will go away :) Cheers, David. -- David Fetter <david@xxxxxxxxxx> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@xxxxxxxxx Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
