Geek Answers Handbook

SQL Select the two maximum rows from one table and join the third table

Two tables in the mix:

the details

| item_id | user_id | data |
----------------------------
| 10       | 100    | A    |
| 11       | 100    | C    |
| 12       | 101    | E    |
| 13       | 101    | G    |

item_detail

| id | item_id | ignore | detail1 | detail2 | detail3 | detail4 |
-----------------------------------------------------------------
| 1  | 10      |   0    |   h1    |   h2    |  h3     |  h4     |
| 2  | 10      |   0    |   g1    |   g2    |  g3     |  g4     |
| 3  | 10      |   1    |   f1    |   f2    |  f3     |  f4     |
| 4  | 11      |   0    |   e1    |   e2    |  e3     |  e4     |
| 5  | 11      |   0    |   d1    |   d2    |  d3     |  d4     |
| 6  | 11      |   1    |   c1    |   c2    |  c3     |  c4     |
| 7  | 12      |   0    |   b1    |   b2    |  b3     |  b4     |
| 8  | 13      |   0    |   a1    |   a2    |  a3     |  a4     |

I need to find detail1, detail2 from the MAX id of the item_detail item_id element and detail3, detail4 from the MAX NON IGNORED ID item_detail by item_id and show the line with the position.
Expected Result:

| item_id | user_id | data | detail1 | detail2 | detail3 | detail4 |
--------------------------------------------------------------------
| 10      | 100     | A    |   f1    | f2      | g3      | g4      |       
| 11      | 100     | C    |   c1    | c2      | d3      | d4      |
| 12      | 101     | E    |   b1    | b2      | b3      | b4      |
| 13      | 101     | G    |   a1    | a2      | a3      | a4      |

Here is the SQLFiddle with these datasets: http://sqlfiddle.com/#!2/52839
Any takers? Your input is greatly appreciated. Thank!

+4
source share
2 answers

The easiest way is to use group_concat()with substring_index():

select i.item_id, i.user_id, i.data,
       substring_index(group_concat(id.detail order by id.id desc), ',', 1
                      ) as last_detail,
       substring_index(group_concat(case when id.ignored = 0 then id.detail1 end order by id.id desc), ',', 1
                      ) as last_non_ignored_detail1
       substring_index(group_concat(case when id.ignored = 0 then id.detail2 end order by id.id desc), ',', 1
                      ) as last_non_ignored_detail2
       substring_index(group_concat(case when id.ignored = 0 then id.detail3 end order by id.id desc), ',', 1
                      ) as last_non_ignored_detail3
       substring_index(group_concat(case when id.ignored = 0 then id.detail4 end order by id.id desc), ',', 1
                      ) as last_non_ignored_detail4
  from items i join
       item_detail id
       on i.item_id = id.item_id
 group by i.item_id;
+2
source

...

SELECT i.* 
     , x.detail last_detail
     , y.detail last_non_ignored_detail
  FROM items i
  LEFT
  JOIN 
     ( SELECT a.* 
         FROM item_detail a 
         JOIN 
            ( SELECT item_id
                  , MAX(id) max_id 
               FROM item_detail 
              GROUP 
                 BY item_id
            ) b 
           ON b.item_id = a.item_id 
          AND b.max_id = a.id
     ) x
    ON x.item_id = i.item_id
  LEFT
  JOIN  
     ( SELECT a.* 
         FROM item_detail a 
         JOIN 
            ( SELECT item_id
                  , MAX(id) max_id 
               FROM item_detail
              WHERE ignored = 0
              GROUP 
                 BY item_id
            ) b 
           ON b.item_id = a.item_id 
          AND b.max_id = a.id
     ) y
    ON y.item_id = i.item_id;

... - .

, ...

SELECT i.*
     , MAX(d.detail) x
     , MAX(CASE WHEN d.ignored = 0 THEN d.detail END) y
  FROM items i 
  JOIN item_detail d 
    ON d.item_id = i.item_id 
 GROUP  
    BY item_id;

, IGNORE , . ( !)

+5

More articles:


All Articles