Find best picture for each category

Question

I have a database (mysql) with two tables, one for categories and one for photos.

Not all categories have photos, but the ones with photos are marked in the categories table with a flag photo=yes. There is also a flag for the domestic categories, which are the ones I'm interested in here. The Photos have grades from 1-10.

What I want to do is to rank the categories flagged domestic=yes with the worst photos (so I know what to improve). Since there are 700 categories and about 7.000 photos, it would be nice to search. :-)

Categories Table

ID     Category_name    Photo    Domestic
1      CatA             yes      yes
2      CatB             yes      yes
3      CatC             yes      yes
4      CatD             yes      no
5      CatE             no       yes

Photo table

ID    Cat_ID    Grade
1     1         4
2     1         4
3     1         5
4     2         6
5     2         6
6     3         4
7     3         4
8     3         3
9     4         2

So the result from my search I would like to get is the domestic=yes categories that have photo=yes ranked by category with the worst best pic:

Category:    Grade:
CatC         4
CatA         5
CatB         6

I hope I have been clear, this would really help me, so I'm looking forward to your solutions. :-)

Thanks in advance!

*"so I'm looking forward to your solutions"* -- have you tried anything? [so] is not a coding service. You have to put some effort in it. — axiac, Nov 21 '17 at 10:09
and see: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Nov 21 '17 at 10:09
There are dozens of similar questions under the tag [tag:greatest-n-per-group]. — axiac, Nov 21 '17 at 10:11
Please search before posting, as this has been discussed to distraction already. Also, if you must post, show some evidence that you've done anything before asking others to do it for you. one of the many duplicates: [Using LIMIT within GROUP BY to get N results per group?](https://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group) — underscore_d, Nov 21 '17 at 10:28
Yes, I have tried a lot, realizing I don't fix this. I have tried to select the Categories applicable (photo and domestic = yes) and finding the id's in photo-table by using "limit" just to understand that does not work. I have posted one question here before, and I will not do that for every little thing I run into. I thought this forum was for questions you could not deal with. I have tried to search for other solutions here for about an hour before making my own question. But if you don't know how to do this, it is really hard to know what to search for. — yens, Nov 21 '17 at 10:55
To clarify more what I have tried, since you don't seem to believe me, what I need from the photo table is something like: select * from photo_table where category_id = X order by grade desc limit 1 But I cannot combine that with the categorytable. The reason for saying that I'm looking forward to the solutions is that I really appriciate that people are trying to help out. When I create a new question, I got to clarify that yes, I have tried myself, and yes, I have searched. — yens, Nov 21 '17 at 11:57

score 0 · Accepted Answer · answered Nov 21 '17 at 10:16

0

You could try :

Select categoryname,max(grade) grade
From categories c join photos p on c.id = p.id and c.domestice = 'yes' group by categoryname;

answered Nov 21 '17 at 10:16

Nikhil Shetkar

346
1
9

That is absolutely brilliant. I had to add some "order by" and so on, and will learn a lot by analyzing the sql you did for me! :-) – yens Nov 21 '17 at 13:06
Good to know that :) . But next time first try from your end and if not able to get then search similar questions and if that also not helped then finally post your question mentioning whatever you have tried so far.... – Nikhil Shetkar Nov 21 '17 at 13:13
I have tried! I promise you, see my answers on the comments above. When I enter the "create question"-section, I pass a page stating that I should try myself, and search. I have done both, but if you don't know what the solution you are searching for is, the searching is very hard. I have found many answers on this website, but to this question, I could not find a one where I understood the answer applied to my situation. – yens Nov 21 '17 at 13:19
Thanks, but it is frustrating to be questioned. I understand a lot of people post just because it is easier than trying, but I'm not one of them. I'll try to find another forum, it was not my intention to make anyone annoyed. – yens Nov 21 '17 at 13:47
hey its okay no one is annoyed here. They just want everyone to learn by themselves and if you say you have tried then I believe you. :D – Nikhil Shetkar Nov 21 '17 at 14:45

Find best picture for each category

1 Answers1