Mysql select latest input within each group

Question

So I have this table named

maid_processing
id     code    confirm_arrival    category_id    arrived 
5       A1     2014-01-02          NULL            1
4       B2     2014-01-02          NULL            1
3       A1     2014-01-01          NULL            1
2       B2     2014-01-01          NULL            1
1       B2     2014-01-01          NULL            0

So as you can see, the codes are repeated but other columns might differ. What I want to achieve is that I want to

Select all codes where the confirm_arrival field satisfies a given range of date, and arrived=1, and since A1 and B2 will be repeated twice since the example satisfies the condition Then we can group those with same code together, hence, 2 groups are formed, A1 and B2

So what I ultimately want is that within these 2 groups, I only want the row of input within each groups with the id number=biggest(hence latest).

    The desired result will be 
id   code    confirm_arrival
5    A1      2014-01-02 
4    B2      2014-01-02

What I have so far will return only the earliest(id=smallest within each group), see the code below

SELECT * 
  FROM 
     ( SELECT * 
         FROM maid_processing mp
         LEFT
         JOIN 
            ( SELECT id catid
                   , archived 
                FROM category
            ) AS cat 
           ON cat.catid = maid_mp.category_id 
        WHERE arrived = 1 
          AND (archived IS NULL OR archived = 0) 
          AND confirm_arrival BETWEEN :from_date AND :to_date     
        ORDER 
           BY confirm_arrival DESC
     ) AS inv 
 GROUP 
    BY code;

If you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. Also, make archived not nullable and set it with a default value of 0. Oh, and wouldn't the dates need to be enclosed in inverted commas? — Strawberry, Feb 10 '15 at 12:19
it is actually under model()->findAllBySql using Yii, im not sure whether the syntax matters here — Cheong D Mun Pong, Feb 10 '15 at 13:01
possible duplicate of [Get records with max value for each group of grouped SQL results](http://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) and [Get records with max value for each group of grouped SQL results](http://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column) — Salman A, Feb 10 '15 at 13:02

score 0 · Answer 1 · edited Feb 10 '15 at 14:30

0

SELECT * 
  FROM 
     ( SELECT * 
         FROM maid_processing mp
         LEFT
         JOIN 
        ( SELECT id catid
               , archived 
            FROM category
        ) AS cat 
       ON cat.catid = mp.category_id 
    WHERE arrived = 1 
      AND (archived IS NULL OR archived = 0) 
      AND confirm_arrival BETWEEN :from_date AND :to_date     
    ORDER 
       BY id DESC
 ) AS inv 
 GROUP 
BY code;

I just replaced the confirm_arrival with id in the 4th last line.

edited Feb 10 '15 at 14:30

Strawberry

33,750
13
40
57

answered Feb 10 '15 at 13:21

Cheong D Mun Pong

247
1
2
10

This solution relies on an undocumented hack (unless you count the comments section of the relevant page of the manual). For this reason, I think it's better to stick with the documented solutions. – Strawberry Feb 10 '15 at 14:36

Mysql select latest input within each group

1 Answers1