CODEWITHSUNDEEP
sqlsql-server
Javid Hesenov
Javid Hesenov

Reputation: 11

How to find 5th city which sell more products in MSSQL / SQL Server

I have queried so far and it is all right. The only thing I want is to find 5th city but instead it queries all 5 cities. I will appreciate if anybody can give me a hand. Thanks ahead.

SELECT TOP 5 SUM    (fact.sale.Quantity) as quantity,   Dimension.City.City     FROM    fact.Sale 
INNER JOIN    Dimension.City
on   fact.Sale.[City Key] = Dimension.city.[City Key]
GROUP BY Dimension.city.City
ORDER BY SUM(quantity)    desc

Upvotes: 0

Views: 106

Answers (2)

JK Chai
JK Chai

Reputation: 145

@javid-hesenov I think SQL code below is what @Arjun meant. @Arjun please verified. Thanks.

WITH CTE AS (
SELECT SUM(fact.sale.Quantity) as quantity, 
       Dimension.City.City as city,
       ROW_NUMBER() OVER (ORDER BY SUM(fact.sale.Quantity) DESC) as index  
FROM fact.Sale 
INNER JOIN Dimension.City
on fact.Sale.[City Key] = Dimension.city.[City Key]
GROUP BY Dimension.city.City
)
SELECT quantity, city
FROM CTE
WHERE index = 5;

UPDATE: Solution without Common Table Expression.

SELECT quantity, city 
FROM (
    SELECT SUM(fact.sale.Quantity) as quantity, 
           Dimension.City.City as city,
           ROW_NUMBER() OVER (ORDER BY SUM(fact.sale.Quantity) DESC) as index  
    FROM fact.Sale 
    INNER JOIN Dimension.City
    on fact.Sale.[City Key] = Dimension.city.[City Key]
    GROUP BY Dimension.city.City
) as tbl_sq
WHERE index = 5;

Upvotes: 0

Arjun
Arjun

Reputation: 31

Top 5 will return all top 5 city.

Instead use row_number() function to add index and select 5th index.

Upvotes: 2

Related Questions