select top x in UNION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select top x in UNION

Author

Topic

CLages
Posting Yak Master

116 Posts

Posted - 2005-03-15 : 17:56:28

hi, its me again with another trouble

i have this simple select below

once i put top 20 in each select and once when i use union
i can not use (order by for in each select) I MUST USE "order by only
in the end of script)
my Select works wrong because when i use "order by cli_uf , ParticUF DESC "
SELECT top 20 brings me up 20 record for each Group by (in this case)
UF (state in english)

something like this ( wrong)

UF bla bla bla %ParticUF
BA --- --- --- 12
BA ---- --- --- 08
BA ---- --- --- 06

RJ ---- --- --- 05
RJ ---- --- --- 04
RJ ---- --- --- 02

the right one would be

UF bla bla bla %ParticUF
BA --- --- --- 12
BA ---- --- --- 08
BA ---- --- --- 06

RJ ---- --- --- 33
RJ ---- --- --- 27
RJ ---- --- --- 12

I want to get the 20 rows of each Group from the Biggest to smallest using %ParticUF
if i dont use TOP 20 works fine but i get too many rows

tks
C. Lages

Select top 20 cli_uf,
cli_codigo,
cli_fantasia,
sum(mov_qt) as Qt,
sum(mov_mercadoria ) as Valor,
VltotalUF =( select sum(mov_mercadoria )
from moviment , natureza , clientes
where mov_codfis = nat_codigo
and mov_cliente = cli_codigo
and mov_flag = 2
and nat_grupo = 'A'
and cli_uf = 'BA') ,

particUF =( sum(mov_mercadoria) /
(select sum(mov_mercadoria )
from moviment , natureza , clientes
where mov_codfis = nat_codigo
and mov_cliente = cli_codigo
and mov_flag = 2
and nat_grupo = 'A'
and cli_uf = 'BA') ) * 100

from moviment, clientes, natureza
where (mov_cliente = cli_codigo )
and (mov_codfis = nat_codigo)
and (mov_flag = 2)
and (nat_grupo = 'A')
and (cli_uf = 'BA')

group by
cli_uf, cli_codigo, cli_fantasia

union all

select top 20 cli_uf,
cli_codigo,
cli_fantasia,
sum(mov_qt) as Qt,
sum(mov_mercadoria ) as Valor,
VltotalUF =( select sum(mov_mercadoria )
from moviment , natureza , clientes
where mov_codfis = nat_codigo
and mov_cliente = cli_codigo
and mov_flag = 2
and nat_grupo = 'A'
and cli_uf = 'RJ') ,

particUF =( sum(mov_mercadoria ) /
(select sum(mov_mercadoria )
from moviment , natureza , clientes
where mov_codfis = nat_codigo
and mov_cliente = cli_codigo
and mov_flag = 2
and nat_grupo = 'A'
and cli_uf = 'RJ' ) ) * 100

from moviment, clientes, natureza
where (mov_cliente = cli_codigo )
and (mov_codfis = nat_codigo)
and (mov_flag = 2)
and (nat_grupo = 'A')
and (cli_uf = 'RJ')
group by
cli_uf, cli_codigo, cli_fantasia

order by cli_uf , ParticUF DESC
GO

gpl
Posting Yak Master

195 Posts

Posted - 2005-03-15 : 19:41:48

How about this, it puts your unioned selects into a derived table, then sorts and tops off the 20 wanted rows (I havent tried this as I dont have the table structures, but in theory it would work)

Graham


Select top 20 cli_uf,
cli_codigo,
cli_fantasia, 
Qt,
Valor,
VltotalUF
from
(
Select cli_uf,
cli_codigo,
cli_fantasia, 
sum(mov_qt) as Qt,
sum(mov_mercadoria ) as Valor,
VltotalUF =( select sum(mov_mercadoria ) 
from moviment , natureza , clientes
where mov_codfis = nat_codigo
and mov_cliente = cli_codigo
and mov_flag = 2
and nat_grupo = 'A'
and cli_uf = 'BA') ,

particUF =( sum(mov_mercadoria) /
(select sum(mov_mercadoria ) 
from moviment , natureza , clientes
where mov_codfis = nat_codigo
and mov_cliente = cli_codigo
and mov_flag = 2
and nat_grupo = 'A'
and cli_uf = 'BA') ) * 100

from moviment, clientes, natureza
where (mov_cliente = cli_codigo )
and (mov_codfis = nat_codigo)
and (mov_flag = 2)
and (nat_grupo = 'A')
and (cli_uf = 'BA')

group by
cli_uf, cli_codigo, cli_fantasia

union all

select cli_uf,
cli_codigo,
cli_fantasia,
sum(mov_qt) as Qt,
sum(mov_mercadoria ) as Valor,
VltotalUF =( select sum(mov_mercadoria ) 
from moviment , natureza , clientes
where mov_codfis = nat_codigo
and mov_cliente = cli_codigo
and mov_flag = 2
and nat_grupo = 'A'
and cli_uf = 'RJ') ,

particUF =( sum(mov_mercadoria ) /
(select sum(mov_mercadoria ) 
from moviment , natureza , clientes
where mov_codfis = nat_codigo
and mov_cliente = cli_codigo
and mov_flag = 2
and nat_grupo = 'A'
and cli_uf = 'RJ' ) ) * 100


from moviment, clientes, natureza
where (mov_cliente = cli_codigo )
and (mov_codfis = nat_codigo)
and (mov_flag = 2)
and (nat_grupo = 'A')
and (cli_uf = 'RJ')
group by
cli_uf, cli_codigo, cli_fantasia
) As Derived

order by cli_uf , ParticUF DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources