Top 3 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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s