Tech Crumbs : Top 10 for every group

 

Top 10 for every group

I have a table, which keeps the country, Referer URL and their counts.

I need to find out the top 2 URL’s for every country.

So, you see this is not a straight top two order by problem. If you know the solution then it is very simple, if not then you may spend some time figuring out. Hopefully this will save your few minutes.

Here is the solution:

  CREATE TABLE Referer
(
CountryId VARCHAR (100),
RefererId VARCHAR (100),
Counts INT
)

INSERT INTO Referer VALUES ('US', 'http:\\microsoft.com', 10345)
INSERT INTO Referer VALUES ('US', 'http:\\microsoft.com\SQL', 43)
INSERT INTO Referer VALUES ('US', 'http:\\microsoft.com\Office', 234)
INSERT INTO Referer VALUES ('US', 'http:\\microsoft.com\XML', 2313)
INSERT INTO Referer VALUES ('US', 'http:\\microsoft.com\SQL', 105)
INSERT INTO Referer VALUES ('UK', 'http:\\microsoft.com', 23)
INSERT INTO Referer VALUES ('UK', 'http:\\microsoft.com', 10734)
INSERT INTO Referer VALUES ('UK', 'http:\\microsoft.com\SQL', 10438)
INSERT INTO Referer VALUES ('UK', 'http:\\microsoft.com\SQL', 1039)
INSERT INTO Referer VALUES ('UK', 'http:\\microsoft.com\Office', 14310)
INSERT INTO Referer VALUES ('Canada', 'http:\\microsoft.com', 14151)
INSERT INTO Referer VALUES ('Canada', 'http:\\microsoft.com', 1412)
INSERT INTO Referer VALUES ('Canada', 'http:\\microsoft.com\SQL', 15613)
INSERT INTO Referer VALUES ('Canada', 'http:\\microsoft.com', 14134)
INSERT INTO Referer VALUES ('Canada', 'http:\\microsoft.com\SQL', 13145)
GO

WITH RefererTemp AS
(
  SELECT ROW_NUMBER() OVER(PARTITION BY CountryId ORDER BY  Counts DESC) AS 'RowNumber' , *
  FROM Referer
)
SELECT * FROM RefererTemp
WHERE RowNumber < 3

RowNumber	countryId	RefererId	Count
1	Canada	http:\\microsoft.com\SQL	15613
2	Canada	http:\\microsoft.com	14151
1	UK	http:\\microsoft.com\Office	14310
2	UK	http:\\microsoft.com	10734
1	US	http:\\microsoft.com	10345
2	US	http:\\microsoft.com\XML	2313

Sure, there will be other way to solve the problem. Let me know.

Tech Crumbs : Top 10 for every group