From: | David Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Edson Richter <edsonrichter(at)hotmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Re : Query "top 10 and others" |
Date: | 2014-07-05 00:20:40 |
Message-ID: | CAKFQuwadPzUJWBi8gFiOJFwuUQvCzDqJ1n2L=1-B8F-fwZmVxw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
>
> > with QRY as (select C1.country, C1.state, sum(C1.population)
> > from places C1
> > group by 1, 2
> > order by 3 DESC
> > limit 10)
> >
> > select * from QRY
> > union
> > select 'others' as "country", '' as "state", sum(population)
> > from places
> > where not exists (select 1 from QRY where country = QRY.country and state
> > = QRY.state)
> >
>
> (not tested)
with QRY as ( SELECT country, state, sum(population) as st_pop FROM places
GROUP BY country, state )
, u1 AS ( SELECT country, state, st_pop FROM QRY ORDER BY st_pop DESC
LIMIT 10 )
, u2 AS ( SELECT 'other' AS country, '' AS state, sum(st_pop) FROM QRY
WHERE NOT EXISTS (
SELECT 1 FROM u1 WHERE (QRY.country, QRY.state) = (u1.country, u1,state)
)
SELECT * FROM u1
UNION ALL
SELECT * FROM u2
;
David J.
From | Date | Subject | |
---|---|---|---|
Next Message | Madhurima Das | 2014-07-05 02:06:10 | memory leak while trying to update/alter column in postgresql |
Previous Message | Edson Richter | 2014-07-04 23:34:13 | Re : Query "top 10 and others" |