Professional Web Applications Themes

Eliminating duplicates - Microsoft SQL / MS SQL Server

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

  1. #1

    Default Eliminating duplicates

    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


    Dave Guest

  2. #2

    Default Re: Eliminating duplicates

    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

    Bob Barrows Guest

Similar Threads

  1. Eliminating hard returns in OCR
    By scottws@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 2
    Last Post: December 19th, 04:10 PM
  2. Eliminating Doubles in SQL
    By Marsbar24u in forum Dreamweaver AppDev
    Replies: 3
    Last Post: March 16th, 07:40 AM
  3. eliminating red eye
    By larry_selter@adobeforums.com in forum Adobe Photoshop Mac CS, CS2 & CS3
    Replies: 15
    Last Post: February 18th, 09:32 PM
  4. eliminating moire patterns
    By Gary King in forum Adobe Photoshop Elements
    Replies: 1
    Last Post: August 18th, 05:17 AM
  5. Eliminating the border area
    By Peter Lee in forum Macromedia Flash Sitedesign
    Replies: 0
    Last Post: June 28th, 06:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139