mysql select one last row of each type

mysql, select

Solution

You can do the first with this `exists` clause:

select t.*
from table t
where not exists (select 1
                  from table t2
                  where t2.type = t.type and t2.date > t.date
                 );

The maximum in the last 60 minutes is the same as the maximum overall. But to get records that are only from the last 60 minutes, add a `where` condition:

select t.*
from table t
where t.date >= date_sub(getdate(), interval 1 hour) and
      not exists (select 1
                  from table t2
                  where t2.type = t.type and t2.date > t.date
                 );

Note that these queries assume you want the most recent row based on the `date` column. You can do the same logic with the `id` column instead.

Problem

I got table with ID, TYPE, NUMBER, DATE example, without date: ``` 1 a 15 2 c 10 3 b 11 4 b 14 5 a 19 6 c 1 7 b 14 8 b 7 ``` SELECT 1: I want to select 1 last record of each type ... so result will be: ``` 5 a 19 15.2.2014 16:00 8 b 7 15.2.2014 16:50 6 c 1 15.2.2014 17:00 ``` NOW it is 15.2.2014 17:01 SELECT 2: I want to select from "SELECT 1" maximum from each last type last 60 minutes (try to not to do it as "select from (SELECT 1)", result will be: ``` 8 b 7 15.2.2014 16:50 ``` can someone help me ?

Original source