mysql select one last row of each type [closed]

Question

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 ?

Answer 1

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.

Answer 2

SELECT id,type,date from table 
WHERE 
id in (SELECT max(id) FROM table GROUP BY type);