CODEWITHSUNDEEP
sql
user2724983
user2724983

Reputation: 31

Return Top 5 Sales by State

I am trying to write a query that will return the top 5 sales in each State that provides the Debtor Name and the Net Total. I have come up with the following which does do the job, however, I'm sure there is a better way, especially if a new State was added.

I have tried to understand the WHERE IN syntax but not I'm not getting it. I'd appreciate any assistance.

SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "VI"
ORDER BY NetTotal DESC
UNION ALL
SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "NS"
ORDER BY NetTotal DESC
UNION ALL
SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "sa"
ORDER BY NetTotal DESC
UNION ALL
SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "wa"
ORDER BY NetTotal DESC
UNION ALL
SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "ta"
ORDER BY NetTotal DESC
UNION ALL
SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "ac"
ORDER BY NetTotal DESC
UNION ALL
SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "ql"
ORDER BY NetTotal DESC
UNION ALL SELECT  TOP 5 data.DebtorNameShort, data.State, data.NetTotal
FROM data
WHERE State= "nt"
ORDER BY NetTotal DESC;

Upvotes: 3

Views: 2170

Answers (2)

Joachim Isaksson
Joachim Isaksson

Reputation: 180987

Something like this should work;

WITH cte AS (
   SELECT DebtorNameShort, State, NetTotal,
          ROW_NUMBER() OVER (PARTITION BY State ORDER BY NetTotal DESC) rn
   FROM data
)
SELECT DebtorNameShort, State, NetTotal
FROM cte
WHERE rn <= 5;

Here I'm using a common table expression to get the row number of each row ordered by NetTotal descending, per state. That row number can then easily be used in a query to filter out the top 5 rows per state.

Upvotes: 2

Taryn
Taryn

Reputation: 247810

Based on the TOP syntax I am guessing you are using SQL Server. You should be able to use row_number() to return the top 5 values for each state:

select DebtorNameShort, State, NetTotal
from
(
  select DebtorNameShort, State, NetTotal,
    row_number() over(partition by state order by NetTotal desc) seq
  from data 
) d
where seq <= 5

row_number() is a windowing function that will allow to to create a sequential value for each item in a partition - for your example you will partition the data by the state. As long as you order the NetTotal in descending order then you can filter the final result to only return those rows will a row number of 1-5.

Upvotes: 7

Related Questions