Can I join two tables by which the joined table is sorted by a specific column?

Question

I don't really like the database guru, so I need help with the query I'm working on. In my photography community project, I want to richly visualize tags, not only displaying the tag name and counter (the number of images inside them), but also want to show the thumb of the most popular image inside the tag (most of the karma).

The table setting is as follows:

• The image table contains the basic metadata of the image, the karma field is important.
• The image file table contains several entries per image, one for each format
• The tag table contains tag definitions.
• Tag_map table displays tags on images

In my usual trial and authorized error request, I went this far:

SELECT * FROM

(SELECT tag.name, tag.id, COUNT(tag_map.tag_id) as cnt
FROM tag INNER JOIN tag_map ON (tag.id = tag_map.tag_id)
INNER JOIN image ON tag_map.image_id = image.id
INNER JOIN imagefile on image.id = imagefile.image_id 
WHERE imagefile.type = 'smallthumb'
GROUP BY tag.name
ORDER BY cnt DESC)

as T1 WHERE cnt > 0 ORDER BY cnt DESC

[column sentence of internal query compressed for simplicity)

This query gives me what I need. An external query ensures that only tags for which there is at least 1 image are returned. An internal query returns tag data, such as its name, counter (number of images), and thumb. In addition, I can sort the internal query as I see fit (for most images, in alphabetical order, recently, etc.)

So far so good. However, the problem is that this request does not match the most popular image (the karma itself) of the tag; it seems to always accept the latest in the tag.

How can I make sure the most popular image matches the tag?

Answer 1

:

SELECT
  tag.id, 
  tag.name,
  tag_group.cnt,
  tag_group.max_karma,
  image.id, 
  imagefile.filename
  /* ... */
FROM
  tag
  /* join against a list of max karma values (per tag) */
  INNER JOIN (
    SELECT   MAX(image.karma) AS max_karma, COUNT(image.*) cnt, tag_map.tag_id
    FROM     image
             INNER JOIN tag_map ON tag_map.image_id = image.id
    GROUP BY tag_map.tag_id
  ) AS tag_group ON tag_group.tag_id = tag.id
  /* join against a list of image ids (per max karma value and tag) */
  INNER JOIN (
    SELECT   MAX(image.id) id, tag_map.tag_id, image.karma
    FROM     image
             INNER JOIN tag_map ON tag_map.image_id = image.id
    GROUP BY tag_map.tag_id, image.karma /* collapse >1 imgs with same karma */
  ) AS pop_img ON pop_img.tag_id = tag.id AND pop_img.karma = tag_group.max_karma
  /* join against actual base data (per popular image id) */
  INNER JOIN 
    image ON image.id = pop_img.id
  INNER JOIN
    imagefile ON imagefile.image_id = pop_img.id AND imagefile.type = 'smallthumb'

, "max-per-group" : , / ?

: (tag_id, MAX(image.karma)), . , , , , ROW_NUMBER()/PARTITION BY. , , .

Answer 2

'having', !

SELECT tag.name, tag.id, COUNT(tag_map.tag_id) as cnt
  FROM tag 
 INNER JOIN tag_map 
    ON (tag.id = tag_map.tag_id)
 INNER JOIN image 
    ON tag_map.image_id = image.id
 INNER JOIN imagefile 
    on image.id = imagefile.image_id 
 WHERE imagefile.type = 'smallthumb'
 GROUP BY tag.name HAVING COUNT(tag_map.tag_id) > 0
 ORDER BY cnt DESC