sql query DISTINCT problems

Anon

Here's my present query:

SELECT recordID, item, thumbID
FROM $CFG->dbtbl
WHERE category like '$category' AND maingroup like '$maingroup'

Some items in the db are the same but in different places. They do
however have he same recordID. How can I rewrite this to make sure
that only disting recordID items get retrieved. I tried using DISTINCT
but I did not get it to work.

If I use SELECT DISTINCT recordID it works, but SELECT DISTINCT recordID, item, thumbID does not. Any suggestions?

Thanks

Jadow

fatal

try using a sub query...

SELECT item, thumbID FROM $CFG->dbtbl WHERE
recordID = (SELECT DISTINCT recordID from $CFG->dbtbl WHERE category like '$category' AND maingroup like '$maingroup');

I'm not too sure if the '=' sign will work or not, if it doesn't try 'IN' instead