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

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

CodePudding user response：

Top 5 will return all top 5 city.

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

CodePudding user response：

@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;