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

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