ORDER BY在另一个表中找到的大多数行

Question

I have 2 tables, 1 called googleimage and 1 called googleimagefound, googleimagefound has thousands of rows and each row has an image_id column which corresponds to its respective image from googleimage table (id), each image from the googleimage table could have thousands of rows corresponding to it from the googleimagefound table.

What i am trying to do is SELECT all the images from googleimage table and then ORDER it by the amount of rows found in the googleimagefound table.

Example,

googleimage table has 3 rows with the id's 1, 2, 3

googleimagefound table has 100 rows, 50 of them have the image_id 1, 20 of them have the image_id, 2, and 30 of them have the image_id 3

I want to say

SELECT * FROM googleimage ORDER BY most rows found in googleimagefound table where id = googleimagefound.image_id

Any help will be appriciated

Answer 1

Try this way:

SELECT g.*,T1.Found
FROM googleimage g JOIN
(SELECT image_id,COUNT(image_id) as Found
 FROM googleimagefound
 GROUP BY image_id) T1 ON g.image_id=T1.image_id
ORDER BY T1.Found DESC

Result will be like:

id     name    others     Found
--------------------------------
1      NameA   Value1     120
5      NameD   Value4     95
3      NameB   Value2     82