Page 1 of 1

distinct and order by trouble

Posted: Wed Aug 16, 2006 6:33 am
by tomcupr
Hi!

I've got those collumns in PG database to work with - photoid, filename, challid, rating.

What's the query to get unique(distinct) challid and results ordered by rating? Can't figure it out. I need to fetch all given collumns as well.

Thanks for any hints

Cheers

Tom

Posted: Wed Aug 16, 2006 6:52 am
by JayBird
what sql have you got at the moment?

Posted: Wed Aug 16, 2006 7:02 am
by tomcupr
Pimptastic wrote:what sql have you got at the moment?
Tried loads but didn't find how to do it.

Atm I it works for me with SELECTing DISTINCT challid, insterting them to array and then doing foreach for that array and SELECTING particular photoid ORDERED BY rating LIMIT 1. That first query is LIMIT 16 so it's not so bad but I'm sure that I should be able to do that with one query

Posted: Wed Aug 16, 2006 7:13 am
by CoderGoblin
SELECT DISTINCT ON (challid) * FROM <tablename> ORDER BY challid,ranking

I think that should work

Posted: Wed Aug 16, 2006 7:48 am
by tomcupr
It does. Thanks a million