How to aggregate multiple rows in nested SELECT query Postgresql

I am quite unfamiliar to complex SQL requests ...

What I am trying to achieve is to display of map of active weather alerts on a leaflet map page.

Started by filling a Postgis table with the RSS feed from national weather agencies, then create a view by selecting the ROWs with an expired date later than the actual date and publishing this view through a Geoserver WFS service.

Looked nice except that each geographic area add multiple event-effective-expires features leading to a sort of multiple layers wher only the top event was displayed.

The issue I have is that for each area/polygon I have multiples lines for miscellaneous events/dates that I have to sort out.

The initial table as a structure like :

     areadesc(county)|event(type of alert)|effective(date)|expires(date)|severity(low,medium,severe)

What I did to try sort this mess was: first create a view per event to have only the latest expires alert of "such event" per location:

SELECT DISTINCT ON (country.areadesc) 
    country.areadesc,
    country.event,
    country.effective,
    country.expires,
    country.severity
FROM 
    country
WHERE 
    country.expires >= now() AND now() >= country.effective 
    AND country.event::text = 'Thunderstorms'::text
ORDER BY 
    country.areadesc, country.expires;

I did this for each event I was interested by.

Second I created an aggregated view for each area with the content of the relevant event type :

SELECT DISTINCT 
    country.areadesc,
    country_thunderstorms.severity AS thunderstorms_severity,
    country_thunderstorms.effective AS thunderstorms_effective,
    country_rain.effective AS rain_effective,
    country_rain.expires AS rain_expires,
    country_districts.geom
FROM 
    country_alerts
LEFT JOIN 
    country_thunderstorms ON country.areadesc::text = country_thunderstorms.areadesc::text
LEFT JOIN 
    country_wind ON country.areadesc::text = country_wind.areadesc::text
LEFT JOIN 
    country_rain ON country.areadesc::text = country_rain.areadesc::text
LEFT JOIN 
    country_districts ON country.areadesc::text = country_districts.name_en::text
WHERE 
    country.expires >= now() AND now() >= country.effective 
    AND (country.title::text = ANY (ARRAY['Thunderstorms'::character varying, 'Wind'::character varying, 'Rain'::character varying]::text[]))
ORDER BY 
    country.areadesc;

All this stuff sort of do the job but looks to quite of a hammer to kill a fly.

I am sure there is a way to achieve this in a single run with nested SELECT but absolutely can't figure how :-(

Any suggestion is welcome , thanks for your help.

Solution

I'm not sure if I have answered your question but you can try window functions, like dense_rank() to rank your row.

SELECT 
country,
event_type,
effective_date,
expires,
severity,
dense_rank() 
OVER(PARTITION BY country,event_type 
     ORDER BY effective_date desc, expires desc) as rnk
FROM weather

then write an outer query on it to get only the first rank.

SELECT * FROM (
SELECT 
country,
event_type,
effective_date,
expires,
severity,
dense_rank() 
OVER(PARTITION BY country,event_type 
     ORDER BY effective_date desc, expires desc) as rnk
FROM weather) X WHERE rnk=1

country	event_type	effective_date	expires	severity	rnk
India	Rain	2021-08-01T00:30:01Z	2021-08-01T00:36:34Z	Medium	1
India	Rain	2021-08-01T00:22:01Z	2021-08-01T02:03:34Z	Medium	2
India	Rain	2021-08-01T00:00:01Z	2021-08-01T00:24:34Z	Medium	3
India	thunderstorm	2021-08-01T00:32:01Z	2021-08-01T00:32:34Z	Low	1
India	thunderstorm	2021-08-01T00:30:01Z	2021-08-01T00:23:34Z	Medium	2
India	thunderstorm	2021-08-01T00:11:01Z	2021-08-01T00:10:34Z	High	3
India	Wind	2021-08-01T00:10:01Z	2021-08-01T01:05:34Z	Low	1
India	Wind	2021-08-01T00:00:01Z	2021-08-01T04:01:34Z	Low	2
India	Wind	2021-08-01T00:00:01Z	2021-08-01T00:25:34Z	Low	3

Edit

Based on your answer I understand that you are looking for a pivot result like below.

select * from weather where rnk=1;

country	event_type	effective_date	expires	severity	rnk
India	Rain	2021-08-01T00:30:01Z	2021-08-01T00:36:34Z	Medium	1
India	thunderstorm	2021-08-01T00:32:01Z	2021-08-01T00:32:34Z	Low	1
India	Wind	2021-08-01T00:10:01Z	2021-08-01T01:05:34Z	Low	1

select 
country, 
max(case when (event_type='Rain') then severity else NULL end) as         
Rain_Severity,
max(case when (event_type='Rain') then effective_date else NULL end) as 
Rain_Effective_date,
max(case when (event_type='Rain') then expires else NULL end) as 
Rain_expires,
max(case when (event_type='thunderstorm') then severity else NULL end) 
as Thunderstorm_Severity,
max(case when (event_type='thunderstorm') then effective_date else NULL 
end) as Thunderstorm_Effective_date,
max(case when (event_type='thunderstorm') then expires else NULL end) as 
Thunderstorm_expires,
max(case when (event_type='Wind') then severity else NULL end) as 
Wind_Severity,
max(case when (event_type='Wind') then effective_date else NULL end) as 
Wind_Effective_date,
max(case when (event_type='Wind') then expires else NULL end) as 
Wind_expires
from weather 
where rnk=1
group by country;

country	rain_severity	rain_effective_date	rain_expires	thunderstorm_severity	thunderstorm_effective_date	thunderstorm_expires	wind_severity	wind_effective_date	wind_expires
India	Medium	2021-08-01T00:30:01Z	2021-08-01T00:36:34Z	Low	2021-08-01T00:32:01Z	2021-08-01T00:32:34Z	Low	2021-08-01T00:10:01Z	2021-08-01T01:05:34Z