<span style="font-size:10pt;"><p style="margin-top:0;margin-bottom:0;">Thanks!</p><p style="margin-top:0;margin-bottom:0;">I'll investigate (explain) performance for both versions.</p><p style="margin-top:0;margin-bottom:0;"> </p><dev3_jjy>Atenciosamente,<br><br>Edson Richter </dev3_jjy><br><br>------ Mensagem original ------<br><b>De: </b>David Johnston <david(dot)g(dot)johnston(at)gmail(dot)com><br><b>Data: </b>04/07/2014 21h20<br><b>Para: </b>Edson Richter;<br><b>Cc:</b>pgsql-general(at)postgresql(dot)org;<br><b>Assunto:</b>Re: Re : [GENERAL] Query "top 10 and others"<br><br><div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><span style="font-size:10pt"><pre>> 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)
> </pre></span></blockquote><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">(not tested)</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">
with QRY as ( SELECT country, state, sum(population) as st_pop FROM places GROUP BY country, state )</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">, u1 AS ( SELECT country, state, st_pop FROM QRY ORDER BY st_pop DESC LIMIT 10 )</div>
<div class="gmail_default" style="font-family:arial,helvetica,sans-serif">, u2 AS ( SELECT 'other' AS country, '' AS state, sum(st_pop) FROM QRY WHERE NOT EXISTS (</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">
SELECT 1 FROM u1 WHERE (QRY.country, QRY.state) = (u1.country, u1,state)</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">)</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">
SELECT * FROM u1</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">UNION ALL</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">SELECT * FROM u2</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">
;</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif">David J.</div><div class="gmail_default" style="font-family:arial,helvetica,sans-serif"><br></div></div></div></div>
</span>