SELECT rows with MAX value

Question

Well, I have three different tables (check below). I wanted to make that I can see, which is the latest image (req_image_1, etc) saved for each category, which is not parent (cat_parent = 0)

One table, which holds general information about requests

+----+--------------------------+------------+
| id | req_name                 | req_parent |
+----+--------------------------+------------+
|  3 | Send pack                |         19 |
|  4 | Go Visit                 |         18 |
|  5 | Stop by                  |         19 |
|  6 | Deliver cookies          |         34 |
+----+--------------------------+------------+

Second table, which holds meta information about requests

+----------+------------+------------+----------------------------+
| umeta_id | request_id | meta_key   | meta_value                 |
+----------+------------+------------+----------------------------+
|       1  |          3 | req_city   | London                     |
|       2  |          3 | req_street | 11 Baker street            |
|       3  |          3 | req_img_1  | a1c8f69edb37bf6c6.jpg      |
|       4  |          4 | req_city   | Manchester                 |
|       5  |          4 | req_street | 71 Main street             |
|       6  |          4 | req_img_2  | a71f4160d7f7f7555.jpg      |
|       7  |          5 | req_city   | Sheffield                  |
|       8  |          5 | req_street | 240 Duke street            |
|       9  |          6 | req_city   | Manchester                 |
|       10 |          6 | req_street | 13 Chapel street           |
|       11 |          6 | req_img_1  | 854b9faaa53d8fe02.jpg      |
+----------+------------+------------+----------------------------+

Third table, which holds information about categories

+----+------------------------+------------+
| ID | cat_name               | cat_parent |
+----+------------------------+------------+
|  1 | Category_01            |          0 |
|  6 | Category_02            |          0 |
| 18 | Category_01_01         |          1 |
| 19 | Category_01_02         |          1 |
| 34 | Category_02_01         |          6 |
+----+------------------------+------------+

So far I managed, that I could get all images for each category with this query:

SELECT cat.cat_parent AS category, req.ID, meta.meta_value AS image
FROM d_requests req
LEFT JOIN d_requests_meta meta ON ( req.ID = meta.request_id ) 
LEFT JOIN d_categories cat ON ( req.req_parent = cat.ID ) 
WHERE meta.meta_key LIKE 'req_img_%'

I got this result:

+------------+----+-----------------------+
| category   | ID | image                 |
+------------+----+-----------------------+
|          1 |  3 | a1c8f69edb37bf6c6.jpg |
|          1 |  4 | a71f4160d7f7f7555.jpg |
|          6 |  6 | 854b9faaa53d8fe02.jpg |
+------------+----+-----------------------+

But I wanted to make enhancement, so I would get only result, where each category has only one image against, for example category 1, has image a71f4160d7f7f7555.jpg and category 6 has image 854b9faaa53d8fe02.jpg

I bet, that I miss some basic knowledge, and simple enhancement with subquery and selecting MAX would work as a charm.

Thanks!

I'm out of time for a full answer, but it looks like you got yourself a [tag:greatest-n-per-group] problem. Take a look here: http://stackoverflow.com/a/7745635/570191 — Adriano Carneiro, Oct 09 '12 at 14:42

Clodoaldo Neto · Answer 1 · 2012-10-09T14:57:18.617

SQL Fiddle

select 
    category, 
    (select request_id 
     from d_requests_meta 
     where umeta_id = s.ID 
    ) as ID,
    (select meta_value 
     from d_requests_meta 
     where umeta_id = s.ID 
    ) AS image
from (
    SELECT cat.cat_parent AS category, max(meta.umeta_id) ID
    FROM d_requests req
    LEFT JOIN d_requests_meta meta ON ( req.ID = meta.request_id ) 
    LEFT JOIN d_categories cat ON ( req.req_parent = cat.ID ) 
    WHERE meta.meta_key LIKE 'req_img_%'
    group by cat.cat_parent
) s

score 1 · Accepted Answer · answered Oct 09 '12 at 14:53

SELECT category, ID, image
FROM (  SELECT cat.cat_parent AS category, req.ID, meta.meta_value AS image
        FROM d_requests AS req
        LEFT JOIN d_requests_meta AS meta
        ON req.ID = meta.request_id
        LEFT JOIN d_categories AS cat
        ON req.req_parent = cat.ID
        WHERE meta.meta_key LIKE 'req_img_%'
        ORDER BY req.ID DESC) AS h
GROUP BY category

I edited Clodoaldo's answer with the use of the inofficial MySQL assumption that GROUP BY will return the 1st row based on ORDER BY in subquery.

I changed that answer for a no assumptions one. – Clodoaldo Neto Oct 09 '12 at 15:03 — Clodoaldo Neto, Oct 09 '12 at 15:03

rs. · Answer 3 · 2012-10-09T15:02:33.580

Try this http://sqlfiddle.com/#!2/bfe9a/19

    SELECT Category, ID, Image FROM (
    SELECT Category, ID, Image, 
    @id:=CASE WHEN @category <> category THEN 1 ELSE @id+1 END AS ImgRank,
    @category:=category AS categoryTemp   FROM  
    (SELECT @id:= 0) AS i,
    (SELECT @category:= 0) AS c,    
    (
        SELECT cat.cat_parent AS category, req.ID, meta.meta_value AS image
        FROM d_requests req
        LEFT JOIN d_requests_meta meta ON ( req.ID = meta.request_id ) 
        LEFT JOIN d_categories cat ON ( req.req_parent = cat.ID ) 
        WHERE meta.meta_key LIKE 'req_img_%'
        ORDER BY cat.cat_parent, req.id desc
    ) Vw
  ) vw2 WHERE IMGRANK = 1

check updated query was missing FROM but this returns correct data — rs., Oct 09 '12 at 15:02

SELECT rows with MAX value

3 Answers3