0

I've got the next table (call it alarm_issues).

 
idal | idaction | dt
---- | -------- | -------------------
5    |        6 | 2016-09-23 15:02:50
5    |        7 | 2016-09-23 16:00:00
5151 |        7 | 2016-09-01 00:00:00
5151 |        6 | 2016-12-01 15:35:34

But I need to get the next table from the last:

idal | idaction | dt
---- | -------- | -------------------
5    |        7 | 2016-09-23 16:00:00
5151 |        6 | 2016-12-01 15:35:34

This means that I want "For each group of rows by idal value, I want only the most recent row by keeping tyhe idaction value". I've tried some things, but never works, because the idaction value it loses and mixes with the other (when you group by idaction field).

mp3man
  • 117
  • 9

2 Answers2

1

Here is one method:

select ai.*
from alarm_issues ai
where ai.dt = (select max(ai2.dt)
               from alarm_issues ai2
               where ai2.idal = ai.idal
              );

This uses a correlated subquery to choose the row, for each idal that has the maximum value of dt.

Here is another method:

select ai.*
from (select ai.*,
             (@rn := if(@i = @idal, @rn + 1,
                        if(@i := @idal, 1, 1)
                       )
             ) as rn
      from alarm_issues ai cross join
           (select @rn := 0, @i = 0) params
      order by idal, dt desc
     ) ai
where rn = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks, it's seems it works fine! Unfortunately, my "alarm_issues" table is a generated query and this final sql is pretty longer than yours, but it works! – mp3man Sep 27 '16 at 11:26
0

try this

select t1.* from alarm_issues as t1 inner join
(
select idal,max(dt) as dt from alarm_issues group by idal
) as t2 on t1.idal=t2.idal and t1.dt=t2.dt
Madhivanan
  • 13,470
  • 1
  • 24
  • 29