Re: [GENERAL] limit results to one row per foreign object

2006-06-30 Thread Alan Bullock
"Alan Bullock" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > hi all, I have the following schema: > thanks all for such prompt replies! I'm going with Martijn's solution, I'm a total newbie and it appears simplest (though I realise it's pgsql only) ---

Re: [GENERAL] limit results to one row per foreign object

2006-06-30 Thread Martijn van Oosterhout
If you don't mind using a (quite useful) postgres extension, this might work for you: select distinct on (auctions.id, users.id) * from auctions, bids, users where order by auctions.id, users.id, created_at desc; Hope this helps, On Fri, Jun 30, 2006 at 04:13:12PM +0100, Alan Bullock wrote: >

Re: [GENERAL] limit results to one row per foreign object

2006-06-30 Thread John Sidney-Woollett
Without trying it out, how about something like: select username, maxbid from users u, ( select user_id, max(amount) as maxbid from bids group by user_id where auction_id = XXX ) as b where u.id = b.user_id; John Alan Bullock wrote: hi all, I have the following schema: CREATE TABLE a

Re: [GENERAL] limit results to one row per foreign object

2006-06-30 Thread Alban Hertroys
Alan Bullock wrote: hi all, I have the following schema: CREATE TABLE auctions ( id serial NOT NULL, user_id int4, title varchar(255), body varchar(255), CONSTRAINT auctions_pkey PRIMARY KEY (id) ) WITHOUT OIDS; CREATE TABLE bids ( id serial NOT NULL, auction_id int4, user_id int4, amount f