Question: I have 2 tables containing bookmarks and likes of images in my Pinterest-style application. I want to create a SQL Query where I want to select the most popular images, based on bookmarks and likes.

I want to prioritize bookmarks over likes, but I want an image with 100 likes be ranked higher than an image with 1 bookmark. So for example the result would look like:

* Image with 100 likes
* Image with 75 bookmarks
* Image with 75 likes
* Image with 30 likes
* Image with 20 bookmarks

The tables I got look like the following:

images\_bookmarks
`id|id_image|id_user|id_board`

images\_likes
`id|id_image|id_user`

So I want to create a JOIN where I sort by the count grouped by id\_image. I'm aware of how to create a join but I'm unsure of how to make the sorting behave as I am describing.

Answer: 
(Left) join your image table with your like/bookmark tables, then group and count likes and bookmarks for each row. Then order the result by the criteria you have given (100 likes is "higher" than 1 bookmark)

```
   SELECT *, COUNT(b.id) * 100 + COUNT(l.id) AS rank
     FROM images i
LEFT JOIN images_bookmarks b
       ON i.id = b.image_id
LEFT JOIN images_likes l
       ON i.id = l.image_id
 GROUP BY i.id
 ORDER BY COUNT(b.id) * 100 + COUNT(l.id)

```

If you want to sort by the sum of bookmarkss and likes, but have images with more bookmarks before images with likes, change the ORDER BY clause to `COUNT(b.id) + COUNT(l.id), COUNT(b.id), COUNT(l.id)`
Try a UNION of those tables :

```
SELECT id_image, NUM_OC (
    SELECT id_image, COUNT(id) AS NUM_OC FROM images_bookmarks GROUP BY id_image
  UNION
    SELECT id_image, COUNT(id) AS NUM_OC FROM images_likes il GROUP BY id_image
)
ORDER BY NUM_OC

```

Only issue, which I will figure out, when my the caffeine kicks in, is how to prioritize bookmarks above likes