Getting distinct value

[djones]

I'm having a problem trying to get distinct values. I need the company name to be distinct, but I'm not sure if I have the group by right or not...

 

SELECT ratings.rating_id, ROUND( AVG( ratings.rating_num ) , 2 ) AS rating, gift_company.company_name, gift_item.location_id, gift_company_location.city, gift_company_location.state
FROM ratings, gift_item, gift_company_location, gift_company
WHERE gift_item.gift_item_id = ratings.rating_id
AND gift_item.location_id = gift_company_location.location_id
AND gift_company_location.company_id = gift_company.company_id
GROUP BY rating_id
ORDER BY rating DESC
LIMIT 8 

[fenway]

What does the ratings table look like?

[djones]

id  int(11)

rating_id int(11)

rating_num int(11)

IP varchar(25)

[fenway]

OK... what do you mean by distinct? One set of votes per company, per rating?

[djones]

Some company names are showing up twice because they have 2 or more items that have high ratings. I want the company name to only show once within that 8 limit.

[fenway]

If that's the case, you'll need to grab the distinct list of companies and join that it as well.

[djones]

I'm not exactly sure how to do that

[fenway]

If I understand correctly, you don't want the top 8 rated items, you want a total of 8, but from 8 different companies?

[djones]

Each company has items that are rated. Some companies may have 6 items some may have 1 or 2 items. I need to get the top rated items no matter what, tie it to a company name, and the company name must be distinct in the list of 8.

 

Example

 

Company A

item 1: 5 rating

item 2: 5 rating

 

Company B

item 1: 4 rating

 

Company C

item 1: 3 rating

 

I don't want Company A to show in the list twice because they have 2 items that have a higher rating. I want them to only show once.

[fenway]

That's not so simple... because there's no way to know how many ratings you'll need before you can get 8 distinct companies... you'd have to drop the limit 8, order by company_id, use a user variable to keep track of when it changes, then filter with having... and then ultimately limit in your php app.

[djones]

Alrighty. I can live with the way it is for now. Thanks for your help :-)

[aschk]

There is a way to do this, i just need to clarify some things:

Q. Does each company have more than 1 location, because this is the impression I get from your JOIN syntax.

Q. Each gift (item) is linked to a location? i.e. you can't have the same gift from 2 different companies (or can you)?

Q. Each gift (item) can have multiple ratings. e.g. "flowers" can be rated, 1.3, 4.5, 6.7, and 7.8 ? And this is stored in the ratings table.

 

You appear to be "averaging" then "rounding" your ratings for each gift, which means every gift has an "average" rating. Is this the rating you want to utilise? I'm guessing that it is.

 

So in words, you need the average ratings of each gift, and then display the highest (average) rated gift for each company?