If I had a nickel

christodd — Tue, 07 Oct 2003 00:40:49 +0000

I've done that more than once. The trick here is not to use the DISTINCT tag, but to use the GROUP BY directive.

SELECT id, ordernumber, artist, title, location
FROM `data`
WHERE (content = 'tones') AND (status = 'top')
GROUP BY id
ORDER BY `id` DESC
LIMIT 0, 10

if they have both entries have different id numbers then you'd use
GROUP BY artist, title,location

Hope that help, come to my website for more technical help...

-Chris
Visit http://www.bitesizeinc.net/

andy206uk — Tue, 09 Sep 2003 12:39:52 +0000

buggery... I tried that, but the problem, is that the ordernumber isnt "DISTINCT" is there anyway i can still retrieve the ordernumber, location and id fields while only getting distinct artist and title?

I absolutly *have* to have the ordernumber as it needs to be passed through to allow the user to order the product or hear the demo.

any ideas? I figured i could do another query to get the rest of the details, but thats just a pain in the arse and will slow the page down quite a chunk.

Thanks again.

andy206uk — Tue, 09 Sep 2003 08:33:46 +0000

Thankyou! its strange, but distinct works with one value if you put it in brackets...

SELECT DISTINCT(artist) ....'

I'm not a MySQL wizard (yet - but I'm getting there!) hence why there was a tremendous syntax error!

Thanks again!

Suzanne — Mon, 08 Sep 2003 23:34:39 +0000

Isn't DISTINCT for duplicate entries? They have to be totally identical, though. Any variation and they aren't duplicates.

I do find it somewhat amusing that the first thing is to blame mysql.

SELECT DISTINCT artist, id, ordernumber, title, location FROM 
`data` WHERE (content = 'tones') AND (status = 'top') ORDER BY `id` DESC LIMIT 0, 10

I couldn't find anywhere that had brackets around the thing you want to be distinct. All items you have there will be distinct.