Postgres GROUP BY

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:

Postgres GROUP BY

Post by tomcupr »

Hi!

I'm puling out photos depending on category entered with following code

Code: Select all

SELECT DISTINCT ON(challid) photoid, filename, challid, rating FROM excel_gallery WHERE active > 0 $adultsql AND category = '$searchcat'
but with DISTINCT ON I have no control on rows selected as the query pulls just lates row. Now I need to sellect rows with highest rating but still distinct on challid. I tried many GROUP BY queries but can't figure it out.

Can you help please?

Cheers
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

You're selecting `challid` twice - once in the distinct() function, & one without. In addition to it being more work than necessary, that might be what's breaking your query. Try removing the `challid` field from your list, and add a GROUP BY challid clause.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply