Select last 30 items per group by

Question

Hopefully the title makes any sense.

For this example I'll have the next table in my database

measurements
==================================
stn | date        | temp | time  =
1   | 01-12-2001  | 2.0  | 14:30 =
1   | 01-12-2001  | 2.1  | 14:31 =
1   | 03-12-2001  | 1.9  | 21:34 =
2   | 01-12-2001  | 4.5  | 12:48 =
2   | 01-12-2001  | 4.7  | 12:49 =
2   | 03-12-2001  | 4.9  | 11:01 =
==================================

And so on and so forth.

Each station (stn) has many measurements, one per day second. Now I want to select the temp of each station of the last 30 days measurements where the station has at least 30 temperature measurements.

I was playing with subquerys and group by, but I can't seem to figure it out.

Hope someone can help me out here.

edited the table My example was oversimplified leaving a critical piece of information out. Please review the question.

Answer 1

This is the query that should select Last 30 entries where there are at least 30 entries for a station

This query is based on the answer here by nick rulez, so please upvote him

SELECT t1.stn, t1.date, t1.temp, t1.time FROM 
    (
        SELECT *,
            @num := if(@stn = stn, @num + 1, 1) as rn,
            @stn := stn as id_stn
        FROM 
            `tablename`, 
            (SELECT @stn := 0, @num := 1) as r
        ORDER BY stn asc, date desc
    ) as t1
INNER JOIN 
    (
        SELECT `stn`
        FROM `tablename` 
        GROUP BY `stn`
        HAVING COUNT(*) >= 30
    ) as t
ON t1.stn = t.stn
AND t1.rn <= 30
ORDER BY stn, date desc, time desc

I have tested it on a sample database I made based on your schema and is working fine.

To know more about such queries have a look here Within-group quotas (Top N per group)

Answer 2

SELECT stn, date, temp FROM
(
SELECT stn, date, temp, @a:=IF(@lastStn=stn, @a+1, 1) countPerStn, @lastStn:=stn 
FROM cache 
GROUP BY stn, date
ORDER BY stn, date DESC
) as tempTable 
WHERE countPerStn > 30;

Is the query I was looking for, sorry if my question was 'so wrong' that it pushed you all in the wrong direction. I'll up vote the answers who'm helped me to find the needed query.

Answer 3

select t1.stn,t1.date,t1.temp,t1.rn from (
select *,
   @num := if(@stn = stn, @num + 1, 1) as rn,
   @stn := stn as id_stn
from table,(select @stn := 0, @num := 1) as r
order by stn asc, date desc) as t1
inner join (select `stn`
           from table
          where concat_ws(' ',date,time) >= now() - interval 30 day
          group by `stn`
         having count(*) >= 30) as t
on t1.stn = t.stn
and t1.rn <= 30
order by stn,date desc,time desc