Ben Hamilton Ben Hamilton - 3 months ago 6
SQL Question

Multiple aggregates in Ingres SQL

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?

Answer

I believe in Ingres, you can use first:

select first sch_year_id
from t
group by sch_year_id
having count(*) > 5000
order by sch_year_id desc;
Comments