CODEWITHSUNDEEP
mysqlsql
user3233336
user3233336

Reputation: 27

How to select last and last but one records

I have a table with 3 columns id, type, value like in image below.

example

What I'm trying to do is to make a query to get the data in this format:

type     previous  current
month-1  666       999
month-2  200       15
month-3  0         12

I made this query but it gets just the last value

select * 
  from statistics 
 where id in (select max(id) from statistics group by type)    
 order 
    by type

enter image description here

EDIT: Live example http://sqlfiddle.com/#!9/af81da/1

Thanks!

Upvotes: 0

Views: 115

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

I would write this as:

select s.*,
       (select s2.value
        from statistics s2
        where s2.type = s.type
        order by id desc
        limit 1, 1
       ) value_prev
from statistics s
where id in (select max(id) from statistics s group by type) order by type;

This should be relatively efficient with an index on statistics(type, id).

Upvotes: 4

kc2018
kc2018

Reputation: 1460

select 
type,
ifnull(max(case when seq = 2 then value end),0 ) previous,
max( case when seq = 1 then value end ) current
from
(
select *, (select count(*)
           from statistics s 
           where s.type = statistics.type
           and s.id >= statistics.id) seq
from statistics ) t
where seq <= 2
group by type

Upvotes: 1

Related Questions