Select the 2 latest “group/batch” records from table

Question

I have data like in this mysql table:

id  customer_id  int_proc   inventory
1   A           1           1   
2   A           4           1
3   A           5           1
4   A           0           2
5   A           5           2
6   A           6           2
7   B           6           1       
8   B           7           1
9   B           9           1
10  B           9           2
11  B           9           2
12  C           22          1

I want to get all data from the latest 2 int_proc values for every inventory and where the customer_id is A and B. My result should be like this:

id  customer_id  int_proc   inventory
2   A           4           1
3   A           5           1
5   A           5           2
6   A           6           2
8   B           7           1
9   B           9           1
10  B           9           2
11  B           9           2

Any help is greatly appreciated.

Answer 1

You can use Mysql's user defined variables and give a rank to rows per customer and per inventory with in a same customer group,below query will give 2 latest int_proc per inventory and same customer group if you want to get latest n number of records just change where clause to where t2.r <= n

select 
t2.id,
t2.customer_id,
t2.int_proc, 
t2.inventory
from (
select t.*,
@r:= case when @g = t.customer_id
     then
          case when @sg = t.inventory 
          then  @r+1 
          else 1 end 
      else 1 end r,
@g:= t.customer_id g,
@sg:= t.inventory sg
from test t
cross join (select @g:=null,@sg:=null,@r:=null) t1
  where t.customer_id in('A','B')
order by t.customer_id,t.inventory,t.int_proc desc
  ) t2
where t2.r <= 2
order by id

Fiddle Demo

Edit for duplicate values

If you have duplicated rows for the int_proc you have to add another sub case statement to check for repeated values and rank them accordingly

select 
t2.id,
t2.customer_id,
t2.inventory,
t2.int_proc
from (
select t.*,
@r:= case when @g = t.customer_id
     then
          case when @sg = t.inventory 
          then 
            case when @sr <> t.int_proc
            then  @r+1 
            else @r end 
          else 1 end 
      else 1 end r,
@g:= t.customer_id g,
@sg:= t.inventory sg,
@sr:= t.int_proc sr
from test t
cross join (select @g:=null,@sg:=null,@r:=null,@sr:=null) t1
  where t.customer_id in('A','B')
order by t.customer_id,t.inventory,t.int_proc desc
  ) t2
 where t2.r <= 2
order by id

Fiddle Demo 2

Answer 2

@MKhalidJunaid: your solution worked for quite a while. However, we now get multiple results sets instead of the only two latest int_proc's. Can you please execute the following table at http://www.sqlfiddle.com/#!2/e81fdf/2 (this might be due to the unordered rows of data?)

CREATE TABLE test
    (`id` int, `customer_id` varchar(1), `int_proc` int, `inventory` int)
;

INSERT INTO test
    (`id`, `customer_id`, `int_proc`, `inventory`)
VALUES
    (1, 'A', 1, 1),
    (2, 'A', 4, 1),
    (3, 'A', 5, 1),
    (4, 'A', 0, 1),
    (5, 'A', 5, 1),
    (6, 'A', 6, 1),
    (7, 'A', 6, 1),
    (8, 'A', 7, 1),
    (9, 'A', 9, 1),
    (10, 'B', 91, 2),
    (11, 'B', 92, 2),
    (12, 'B', 93, 2),
    (13, 'B', 95, 2),
    (14, 'B', 95, 2),
    (15, 'C', 22, 1)

;