I have a large table which is split into school years,
sch_year_id i4
name c20
addr1 c20
teacher c20
etc. etc..
sch_year_id is an integer, 23 represents the 13/14 school year, 24 represents the 14/15, etc. etc.
So there are normally thousands of records with the same sch_year_id but for different children.
I want to know the most recent (i.e. max) sch_year_id that contains the most records, but only where the number of records against that sch_year_id is more than 5000.
I can get a listing of the this using a GROUP BY and HAVING as below
select sch_year_id, count(*)
from table
group by sch_year_id
having count(*) > 5000
This return about 10 results, different school years, but I want the max(sch_year_id) of this result set. If I add a max function around sch_year_id in the SELECT, nothing changes in the results.
I can do it with a derived table, as below
select max(a.sch_year_id)
from (select sch_year_id, count(*)
from table
group by sch_year_id
having count(*) > 5000) a
Or use a CTE, but I feel there must be a way of doing it without this?
I believe in Ingres, you can use first:
select first 1 sch_year_id
from t
group by sch_year_id
having count(*) > 5000
order by sch_year_id desc;