MySQL being DISTINCT-ly annoying... ;)

andy206uk posted this at 22:06 — 8th September 2003.

Joined: Jul 2002

Ok... I have problems...

Basically I'm setting up one of those annoying ringtone sites that charge stupid amounts for one of my customers.

I was provided with a CSV database which I imported into MySQL and most of the site works brilliantly, except for a small problem I have on the first page.

if you visit http://www.ringtones.speeding.co.uk you'll see theres a top 10 for monophonic and polyphonic ringtones. Basically the top 10 entries are distinguised because they have a column whos value is "top" if they're in the top 10. so logic would dictate, to select the top 10 ringtones you do this:

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

The only problem is (as you can probably see on the site. For some reason there are multiple instances of each item in the top 10. leaving a rather *ahem* crappy top 10.

i thought to fix this i'd just need to use DISTINCT or something similar to ensure theres only one entry per artist or title... except it doesnt seem to work:

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

It would be easy to remove the duplicates from the database, but since the data will be updated every 2-3 days i don't want to have to worry about little things like this.

can anyone point out the correct syntax to only pull out one entry from each artist where the content is tones and the status is top? It's probably just me being stupid or something...

Thanks!

Andy

Suzanne posted this at 23:34 — 8th September 2003.

She has: 5,507 posts

Joined: Feb 2000

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.

andy206uk posted this at 08:33 — 9th September 2003.

He has: 1,758 posts

Joined: Jul 2002

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!

Andy

andy206uk posted this at 12:39 — 9th September 2003.

He has: 1,758 posts

Joined: Jul 2002

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.

Andy

christodd posted this at 00:40 — 7th October 2003.

They have: 6 posts

Joined: Oct 2003

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/

Visit http://www.bitesizeinc.net/ for more technical help...