March 1, 2005 at 9:42 am
How do I get the top 2 listings from within each grouping from a group by clause. For example, if I had the query below...
Select State, Company, Sum(Revenue)
From myTable
Group By State, Company
Order by State, Company
and each state showed 1000 companies, how would I get the top 2 from each state? This would equate to 100 records in the resultset (50 states X 2 companies for each).
Note: Simply adding top 2 to the above query just gives the top 2 for the complete dataset, not for each grouping.
TIA
Brian
March 1, 2005 at 10:16 am
you could do something like this but I'm sure there's a faster way to return that query... this takes 30 secs to run on 30 groupings / 10k rows)... however the indexes are not optimized (90% of the work is done on scans and bookmark lookups).
Select XType, id, name from dbo.ObjSQL O1 where PkObjSQL IN (Select TOP 2 PkObjSQL from dbo.ObjSQL O2 WHERE O2.XType = O1.XType Order by ID Desc)
ORDER BY XType, id, name
March 1, 2005 at 10:23 am
You may get an elegant soultion to this via sub-queries, but I'd typically solve this with a temp-table (or table variable) that applies a Sequence number to it. Note, in Sql Server 2005, a new T-SQL syntax for ROW_NUMBER() will solve this nicely. Until then:
-- Create an empty temp table with an Identity column to apply a sequence
Select
Identity(int, 1,1) as Sequence,
State,
Company,
Revenue
Into #Sequence
From myTable
Where 0 = 1 -- Create empty
-- Populate it, in ascending State, revenue sequence
Insert Into #Sequence
(State, Company, Revenue)
Select State, Company, Sum(Revenue)
From myTable
Group By State, Company
Order by State, Sum(Revenue) Asc
-- Get the results, self-joinging thru a virtual table to pull the max and max minus 1
-- sequence for each state.
Select s1.State, s1.Company, s1.Revenue
From #Sequence as s1
Inner Join
(
Select s2.State, Max(Sequence) As MaxSeq
From #Sequence As s2
Group By s2.State
) vt
On (s1.State = vt.State And
s1.Sequence = vt.MaxSeq Or s1.Sequence = (vt.MaxSeq - 1)
)
March 1, 2005 at 10:53 am
Here's the solutions that I finally arrived at...
Select State, Company, Revenue
FROM myTable A
WHERE Revenue IN
(
Select TOP 2 Revenue
From myTable B
WHERE B.State = A.State
Order by Revenue DESC
)
Order by State, Revenue DESC
Thanks for the help
Brian
March 1, 2005 at 11:03 am
What if there's a tie for revenue ?
If there's a tie, the sub-query will return you top 2 rows, but if you join that based solely on Revenue, you can & will get more than 2 rows per state in the resultset.
March 1, 2005 at 11:05 am
So this would the the same as "Top n With Ties"
March 1, 2005 at 11:11 am
yes
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply