problem with rankeo on sql server

Question

problem with rankeo on sql server

Navigation

#1 by (3 votes)

0

I have the following query

SELECT em.id_empleado, p.des_familia, RANK() OVER(PARTITION BY p.des_familia ORDER BY sum(venta) DESC) AS RankByVentas,
    sum(venta) as venta, sum(unidades) as unidades     
    FROM dim.empleado as em, fact.venta as vt, dim.producto as p  
    WHERE em.id_empleado = vt.id_empleado AND p.id_producto = vt.id_producto AND vt.id_calendario between 20180101 AND 20181231  GROUP BY em.id_empleado, p.des_familia

Result obtained:

   1     ABRIGOS HOMBRE 1   364812691.0152800000    4167.0000000000
 816     ABRIGOS HOMBRE 2   24366756.0000000000 398.0000000000
 485     ABRIGOS HOMBRE 3   10819031.0000000000 183.0000000000
 373     ABRIGOS HOMBRE 4   4903509.0000000000  54.0000000000
 288     ABRIGOS HOMBRE 5   4663207.0000000000  59.0000000000
3296     ABRIGOS HOMBRE 6   4476416.0000000000  49.0000000000
3185     ABRIGOS HOMBRE 7   4010515.0000000000  48.0000000000
 389     ABRIGOS HOMBRE 8   3747505.0000000000  47.0000000000
3147     BAG    112 117312.0000000000   4.0000000000
  41     BAG    113 116916.0000000000   4.0000000000
3246     BAG    114 113610.0000000000   4.0000000000
 726     BAG    115 113193.0000000000   3.0000000000
1515     BAG    116 112819.0000000000   4.0000000000
3302     BAG    117 108824.0000000000   5.0000000000
2115     BAG    118 104932.0000000000   3.0000000000
1477     BAG    119 104665.0000000000   5.0000000000
 586     BAG    120 104623.0000000000   5.0000000000
 368     BAG    121 103353.0000000000   3.0000000000
1207     BELT   28  1658921.0000000000  83.0000000000
 278     BELT   29  1643719.0000000000  87.0000000000
2886     BELT   30  1629965.0000000000  84.0000000000
 280     BELT   31  1624797.0000000000  84.0000000000
1186     BELT   32  1619111.0000000000  81.0000000000
 143     BELT   33  1599453.0000000000  79.0000000000
1763     BELT   34  1594120.0000000000  83.0000000000
1501     BELT   35  1590595.0000000000  85.0000000000
2674     BELT   36  1585859.0000000000  81.0000000000
3128     BELT   37  1572325.0000000000  82.0000000000

I need to rank the first 5 employees according to the sum of sales of each family of products but when doing the query it shows me ranking from 1 to X in certain families and in others from 144 to Y, I do not understand what happens or how to obtain only the ids of employees from the first 5 places for each family.

What I want to get:

   1     ABRIGOS HOMBRE 1   364812691.01528000  4167.0000000000
 816     ABRIGOS HOMBRE 2   24366756.0000000000 398.0000000000
 485     ABRIGOS HOMBRE 3   10819031.0000000000 183.0000000000
 373     ABRIGOS HOMBRE 4   4903509.0000000000  54.0000000000
2886     ABRIGOS HOMBRE 5   4663207.0000000000  59.0000000000
3147             BAG    1   117312.0000000000   4.0000000000
  41             BAG    2   116916.0000000000   4.0000000000
3246             BAG    3   113610.0000000000   4.0000000000
 726             BAG    4   113193.0000000000   3.0000000000
1515             BAG    5   112819.0000000000   4.0000000000
1207             BELT   1   1658921.0000000000  83.0000000000
 278             BELT   2   1643719.0000000000  87.0000000000
2886             BELT   3   1629965.0000000000  84.0000000000
 280             BELT   4   1624797.0000000000  84.0000000000
1186             BELT   5   1619111.0000000000  81.0000000000

I appreciate the help

sql sql-server

asked by Virginia 27.11.2018 в 14:06

source

1 answer

Add values in columns Multilists linked with ArrayList

score 3 · Accepted Answer

First of all, please do not use JOIN s implicit because they have been deprecated for more than 20 years; try to use JOIN s explicit.

For what you want, the easiest would be to use your current query as a derived table or a CTE, and then filter:

For example, using a CTE:

WITH CTE AS
(
    SELECT  em.id_empleado, 
            p.des_familia, 
            RANK() OVER(PARTITION BY p.des_familia ORDER BY sum(venta) DESC) as RankByVentas,
            SUM(venta) as venta, 
            SUM(unidades) as unidades     
    FROM dim.empleado as em
    INNER JOIN fact.venta as vt
        ON em.id_empleado = vt.id_empleado
    INNER JOIN dim.producto as p  
        ON p.id_producto = vt.id_producto
    WHERE vt.id_calendario BETWEEN 20180101 AND 20181231  
    GROUP BY em.id_empleado, 
             p.des_familia
)
SELECT *
FROM CTE
WHERE RankByVentas <= 5
;

And using a derived table:

SELECT *
FROM (
    SELECT  em.id_empleado, 
            p.des_familia, 
            RANK() OVER(PARTITION BY p.des_familia ORDER BY sum(venta) DESC) as RankByVentas,
            SUM(venta) as venta, 
            SUM(unidades) as unidades     
    FROM dim.empleado as em
    INNER JOIN fact.venta as vt
        ON em.id_empleado = vt.id_empleado
    INNER JOIN dim.producto as p  
        ON p.id_producto = vt.id_producto
    WHERE vt.id_calendario BETWEEN 20180101 AND 20181231  
    GROUP BY em.id_empleado, 
             p.des_familia
    ) X
WHERE RankByVentas <= 5
;