Eliminating duplicates

[Dave]

Hi,

I have a view with the following columns:
member_rating_id
,rating_table_id
,member_id
,screen_name
,rating_value
,number_won
,number_drawn
,number_lost
,points_for
,points_against
,rating_date
,iso_country_code

I need to build a resultset of the top 20 ratings (based on rating_value),
with only one entry for each country, i.e. if there are 5 members from the
USA, I only want to see the one with the highest rating value in the
results.

The following SQL works, but isn't particularly fast:

select top 20
member_rating_id
,rating_table_id
,member_id
,screen_name
,rating_value
,number_won
,number_drawn
,number_lost
,points_for
,points_against
,rating_date
,iso_country_code
from
dbo.vw_member_rating_stats as mrs1
where
member_rating_id = (select top 1
member_rating_id
from
dbo.vw_member_rating_stats as mrs2
where
rating_table_id = @rating_table_id
and
mrs2.iso_country_code =
mrs1.iso_country_code
order by
rating_value desc)
order by
rating_value desc

If anyone can think of a better way of doing this, I'd love to hear it!!

TIA.

Dave

[Bob Barrows]

Please provide sample data and show us (ion tabular form) the results you
want to return from the sample data.

Bob Barrows
Dave wrote:

> Hi,
>
> I have a view with the following columns:
> member_rating_id
> ,rating_table_id
> ,member_id
> ,screen_name
> ,rating_value
> ,number_won
> ,number_drawn
> ,number_lost
> ,points_for
> ,points_against
> ,rating_date
> ,iso_country_code
>
> I need to build a resultset of the top 20 ratings (based on
> rating_value), with only one entry for each country, i.e. if there
> are 5 members from the USA, I only want to see the one with the
> highest rating value in the results.
>
> The following SQL works, but isn't particularly fast:
>
> select top 20
> member_rating_id
> ,rating_table_id
> ,member_id
> ,screen_name
> ,rating_value
> ,number_won
> ,number_drawn
> ,number_lost
> ,points_for
> ,points_against
> ,rating_date
> ,iso_country_code
> from
> dbo.vw_member_rating_stats as mrs1
> where
> member_rating_id = (select top 1
> member_rating_id
> from
> dbo.vw_member_rating_stats
> as mrs2 where
> rating_table_id =
> @rating_table_id and
> mrs2.iso_country_code =
> mrs1.iso_country_code
> order by
> rating_value desc)
> order by
> rating_value desc
>
> If anyone can think of a better way of doing this, I'd love to hear
> it!!
>
> TIA.
>
> Dave