Page 1 of 1

Postgres GROUP BY

Posted: Mon Sep 11, 2006 4:37 am
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

Posted: Mon Sep 11, 2006 3:59 pm
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.