CODEWITHSUNDEEP
sql-servert-sqlunion
Pau Dominguez
Pau Dominguez

Reputation: 188

Unexpected results when using TOP and UNION

I'm using a SQL SERVER 2016.

I have a TRANSACT SQL query that is the union of two selects. I need the TOP 36 values order by some fields.

If the two selects returns values, I want priorice the resultset of the first query over the second. I use the constant field "orden" for this propouse.

But the extrange is that, If the first select do not return registers, the final 36 registers recordset is diferent to the 36 registers I get if I run the second select alone (without the union).

Seams that the TOP clause does not work as spected in UNION statements.

I have remove the TOP 36 clause and manage the resultset by code to get only the 36 first registers.

Am I doing something wrong?

THIS IS THE SQL: 

select TOP 36 1 as orden, year(a.xfecha) as anyo, month(a.xfecha) as mes, b.xarticulo_id, 
c.xunidad_prin_id as unidad, SUM(b.xcantidad_prin) as cantidad,SUM(b.xcantidad_prin*44) 
from Pl_fracli_cab a 
join pl_fracli_lin b 
on a.xempresa_id = b.xempresa_id and a.xciclo_id = b.xciclo_id and a.xseccion_id = b.xseccion_id 
   and a.xtipodoc_id = b.xtipodoc_id and a.xnumdoc_id = b.xnumdoc_id 
join pl_articulos c 
on b.xarticulo_id = c.xarticulo_id where year(a.xfecha) > year(GETDATE()) - 4 and b.xarticulo_id = 'VEN022744' 
and a.xempresa_id = 'TEST' 
group by year(a.xfecha), month(a.xfecha), b.xarticulo_id, c.xunidad_prin_id 

union 

select TOP 36 2 as orden , a.xanyo as anyo, a.xmes as mes, a.xarticulo_id, c.xunidad_prin_id as unidad, 
case c.xunidad_prin_id when 'PZ' then a.xpzs else a.xm2 end as cantidad,xm2*44 
from eqc_hist_ventas a 
join pl_articulos c 
on a.xarticulo_id = c.xarticulo_id and a.xempresa_id = c.xempresa_id 
where a.xanyo > year(GETDATE()) - 4 and a.xarticulo_id = 'VEN022744' and a.xempresa_id = 'TEST' 


order by orden, anyo desc, mes desc;

THESE ARE THE TEN FIRST REGISTERS OF THE FIRST SELECT ALONE:

NONE

THESE ARE THE TEN FIRST REGISTERS OF THE SECON SELECT ALONE: 

2   2019    8   VEN022744   M2  39                  1716
2   2019    7   VEN022744   M2  71,0454545454545    3126
2   2019    6   VEN022744   M2  16,0454545454545    705,999999999998
2   2019    5   VEN022744   M2  20,0227272727273    881,000000000001
2   2019    4   VEN022744   M2  6                   264
2   2019    3   VEN022744   M2  38,0454545454545    1674
2   2019    1   VEN022744   M2  77                  3388
2   2018    12  VEN022744   M2  39                  1716
2   2018    11  VEN022744   M2  14                  616
2   2018    10  VEN022744   M2  13                  572

THESE ARE THE TEN FIRST REGISTERS WITH THE UNION:

2   2018    12  VEN022744   M2  39                  1716
2   2018    11  VEN022744   M2  14                  616
2   2018    10  VEN022744   M2  13                  572
2   2018    9   VEN022744   M2  2                   88
2   2018    8   VEN022744   M2  7,13636363636364    314
2   2018    7   VEN022744   M2  25                  1100
2   2018    6   VEN022744   M2  20,0454545454545    881,999999999998
2   2018    5   VEN022744   M2  88,0227272727273    3873
2   2018    4   VEN022744   M2  5,02272727272727    221
2   2018    3   VEN022744   M2  101                 4444

IN THE UNION THE REGISTERS OF 2019 ARE MISSING.

Upvotes: 0

Views: 89

Answers (2)

JMabee
JMabee

Reputation: 2300

As you can probably tell from the order of your results, you did not use your ORDER BY statement when you ran the second query by itself. Thus the records for 2019 showed up first.

If you want the 2019 records to show up at the top modify your order by clause to include YEAR desc

Upvotes: 0

forpas
forpas

Reputation: 164099

Apply TOP 36 to the UNION of the queries and not to both queries:

select TOP 36 *
from (
  select 1 as orden, year(a.xfecha) as anyo, month(a.xfecha) as mes, b.xarticulo_id, 
  c.xunidad_prin_id as unidad, SUM(b.xcantidad_prin) as cantidad,SUM(b.xcantidad_prin*44) 
  from Pl_fracli_cab a 
  join pl_fracli_lin b 
  on a.xempresa_id = b.xempresa_id and a.xciclo_id = b.xciclo_id and a.xseccion_id = b.xseccion_id 
  and a.xtipodoc_id = b.xtipodoc_id and a.xnumdoc_id = b.xnumdoc_id 
  join pl_articulos c 
  on b.xarticulo_id = c.xarticulo_id where year(a.xfecha) > year(GETDATE()) - 4 and b.xarticulo_id = 'VEN022744' 
  and a.xempresa_id = 'TEST' 
  group by year(a.xfecha), month(a.xfecha), b.xarticulo_id, c.xunidad_prin_id 
  union 
  select 2 as orden , a.xanyo as anyo, a.xmes as mes, a.xarticulo_id, c.xunidad_prin_id as unidad, 
  case c.xunidad_prin_id when 'PZ' then a.xpzs else a.xm2 end as cantidad,xm2*44 
  from eqc_hist_ventas a 
  join pl_articulos c 
  on a.xarticulo_id = c.xarticulo_id and a.xempresa_id = c.xempresa_id 
  where a.xanyo > year(GETDATE()) - 4 and a.xarticulo_id = 'VEN022744' and a.xempresa_id = 'TEST' 
) t
order by orden, anyo desc, mes desc;

Upvotes: 1

Related Questions