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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy