Need help with a query

• Roy Ernest

  SSC-Dedicated

  Points: 38780

  More actions

  February 6, 2008 at 11:50 am

  #182464

  Hi All,

  I have a small problem. I have a table that has a summary report. I have to retrieve the top two rows for country based on the highest number of Hits. Below is the table structure and some data to insert

  CREATE TABLE [dbo].[tbCountryWebHits](

  [Country] [varchar](150) NULL,

  [WebSite] [char](20) NULL,

  [Code] [char](20) NULL,

  [Hits] [float] NULL,

  [uniqueHits] [int] NULL

  ) ON [PRIMARY]

  GO

  Insert into tbCountryWebHits

  Select 'USA','Yahoo.com','XCode',25000,1250

  UNION ALL

  Select 'USA','Yahoo.com','YCode',30000,3500

  UNION ALL

  Select 'USA','CNN.com','ZCode',10000,1000

  UNION ALL

  Select 'UK','Yahoo.com','ACode',20000,1250

  UNION ALL

  Select 'UK','BBC.com','BCode',50000,5250

  UNION ALL

  Select 'UK','BBC.com','CCode',25000,2250

  UNION ALL

  Select 'UK','youtube.com','DCode',60000,8250

  GO

  What I want to get as result is

  UKBBC.com BCode 500005250

  UKyoutube.com DCode 600008250

  USAYahoo.com XCode 250001250

  USAYahoo.com YCode 300003500

  For each country , I want to find the top two rows of data based on the Hits.

  How can I get this. My mind has just gone blank and nothing comes to my mind.

  Please help

  Thanks

  Roy

  -Roy

• antonio.collins

  SSCrazy Eights

  Points: 9600

  More actions

  February 6, 2008 at 11:57 am

  #775734

  quick and dirty, but you get the idea:

  select * from (

  select *, row_number() over (partition by Country order by Hits desc) as num

  from tbCountryWebHits ) as z

  where num <= 2

  if you want to handle ties, use rank()

• Roy Ernest

  SSC-Dedicated

  Points: 38780

  More actions

  February 6, 2008 at 12:06 pm

  #775735

  Anotonio,

  Your quick and dirty trick worked like charm..:)

  Thanks Mate. 🙂

  Roy

  -Roy