distinct and order by trouble

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
tomcupr
Forum Newbie
Posts: 11
Joined: Mon May 08, 2006 2:26 am
Location: Sheffield
Contact:

distinct and order by trouble

Post 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
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

what sql have you got at the moment?
tomcupr
Forum Newbie
Posts: 11
Joined: Mon May 08, 2006 2:26 am
Location: Sheffield
Contact:

Post 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
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

SELECT DISTINCT ON (challid) * FROM <tablename> ORDER BY challid,ranking

I think that should work
tomcupr
Forum Newbie
Posts: 11
Joined: Mon May 08, 2006 2:26 am
Location: Sheffield
Contact:

Post by tomcupr »

It does. Thanks a million
Post Reply