Questions at the bottom of this post

SQL Server 2008

Table has 3 columns:
CompanyName nvarchar(25),
IndustryCode nvarchar(3),
NetSales int

The value in CompanyName column uniquely identifies a record in the
table.

The goal is to produce a result set that - - for each IndustryCode - -
lists the top 5 NetSales amounts and the CompanyName associated with
the NetSales amount. It's possible that more than one company may
have the same NetSales amount...if the following were true:
- more than one company has the same NetSales amount
- those companies also have the same IndustryCode
- the NetSales amount is one of the top 5 NetSales amounts for that
IndustryCode
....if all of the above were true, then, for that particular
IndustryCode, the result set would contain more than 5
records...example...if the table had 50 records with IndustryCode =
'ABC', and the 50 records within IndustryCode (sorted in descending
order of NetSales amount were)

CompanyName IndustryCode NetSales
---------------------- ------------------- -------------
Acme ABC 100,000
Ajax ABC 90,000
Biff ABC 90,000
Chester ABC 75,000
Dimple ABC 59,500
Blarney ABC 50,000
<44 more records>

then the portion of the result set for IndustryCode = 'ABC' would
contain the 6 rows from Acme ... Blarney above; because even though
there are 6 different companies, there are only 5 NetSales amounts
(two companies have NetSales = 90,000).

One general approach to get the desired result set would be:

step 1)
select
CompanyName
IndustryCode
NetSales
FROM OriginalTable
INTO #temp1
ORDER BY
IndustryCode
NetSales desc

all step 1 is doing is sorting the records in OriginalTable into a
temporary table

step 2)
create a temp table:
IndustryCode nvarchar(3)
Amount1 int,
Amount2 int,
Amount3 int,
Amount4 int,
Amount5 int

step 3)
set up a cursor and iterate through #temp1. The iterations would
accumulate the first 5 unique NetSales amounts, and then insert them -
- as one record - - into the temp table from step 2 ... it would then
iterate through records until it encountered a record with a new
IndustryCode ... and then repeat the process for the records with that
IndustryCode

at the end of step 3, the temp table created in step 2 and populated
in step 3 would contain as many records as there are unique values in
IndsutryCode ... and the Amount1...Amount5 columns would identify the
top 5 NetSales amounts for that IndustryCode

step 4)
join the original table with the temp table from steps 2/3 ... join
based on IndustryCode ... and include a record from the table if
NetSales has a value that is equal to Amount1 or Amount2 or Amount3 or
Amount4 or Amount5...something like

select
CompanyName
IndustryCode
NetSales
from OriginalTable Ot
join #temptable2 tt2 on Ot.[IndustryCode] (Ot.[NetSales] = tt2.
[Amount1])
where (
(Ot.[NetSales] = tt2.[Amount1]) OR
(Ot.[NetSales] = tt2.[Amount2]) OR
(Ot.[NetSales] = tt2.[Amount3]) OR
(Ot.[NetSales] = tt2.[Amount4]) OR
(Ot.[NetSales] = tt2.[Amount5])
)

Questions:
1) it seems like there should be away to get the desired result set
using set-based queries (i.e not having to use a cursor) ... is there
a way to get the desired result set without using cursors?
2) it seems like I should be able to use the "TOP 5" clause in the
select statements, but I couldn't figure out how to implement a
solution using SELECT TOP 5 ... is there a way to make use of that?

Thank you.

(mirandacascade (AT) yahoo (DOT) com) writes:
No, TOP will not cut it in this case. But dense_rank will:

WITH CTE AS (
SELECT CompanyName, IndustruCode, NetSales,
rank = dense_rank() OVER(PARTITION BY IndustryCode
ORDER BY NetSales DESC)
FROM tbl
)
SELECT CompanyName, IndustryCode, NetSales
FROM CTE
WHERE rank <= 5
ORDER BY IndustryCode, NetSales DESC


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx