MySQL subquery, get 2 most recent rows of type

Question

I'm trying to get the two latest rows of each type.

ID  TYPE    SCORE
-----------------
1   EUR       2
2   EUR       3
3   CAD       5
4   CAD       6
5   CAD       7
6   CAD       8
7   USD       5
8   USD       3
9   USD       7
10  USD       2

The results returned should be id's:1,2,5,6,9,10.

How do i accomplishthis with mysql subquery? I attempted below..

$sql = "SELECT * FROM `data_analysis_child`
        WHERE type IN 
    (SELECT type FROM `data_analysis_child` ORDER by id DESC LIMIT 2)";

$query = $this->db->query($sql);

How do i solve?

@rams you misunderstood the question. OP wants last 2 _per_ TYPE. So, 6 entries: EUR with 1,2, CAS with 5,6, USD with 9,10 — Alex Tartan, Jul 06 '17 at 13:34

Rams · Answer 1 · 2017-07-06T16:45:42.233

0

You can try this.

   select * from 
(SELECT  t.*,@row_num := IF(@TYPE1=type,@row_num+1,1)AS RowNumber,@TYPE1=type  FROM data_analysis_child t, (SELECT @row_num := 1) x,(SELECT @TYPE1 := '') y
order by type desc,id desc) where RowNumber<=2

edited Jul 06 '17 at 16:45

answered Jul 06 '17 at 13:31

Rams

2,129
1
12
19

This only gets the the two most recent rows. I want the two most recent rows for each `type`.. – mdnba50 Jul 06 '17 at 13:33
sorry I misunderstood, Now I changed my answer plz try that – Rams Jul 06 '17 at 13:51

MySQL subquery, get 2 most recent rows of type

1 Answers1