Search Postgresql Archives

Re : Re : Query "top 10 and others"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Thanks!

I'll investigate (explain) performance for both versions.

 

Atenciosamente,

Edson Richter 


------ Mensagem original ------
De: David Johnston
Data: 04/07/2014 21h20
Para: Edson Richter;
Cc:pgsql-general@xxxxxxxxxxxxxx;
Assunto:Re: Re : [GENERAL] Query "top 10 and others"

> 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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux