Professional Web Applications Themes

Requesting help with complicated query - Microsoft SQL / MS SQL Server

Hi, Sorry, for the lack of info in the title. Just don't know how to simplify it. I have previously been asked to post my questions in a certain way. Unfortunately for a novice, this is not always possible. Please excuse my way of asking this, I think, at least for laymen, this is the simplest way to pose the scenario. Here are a set of records with the most important columns represented. Competitor Name | DivNum | DivDesc | DivRank | PointsThisDay | Tournament Mia Tinkler | 213 | Forms | Black Belt | 100 | Gold Coast Open ...

  1. #1

    Default Requesting help with complicated query

    Hi,

    Sorry, for the lack of info in the title. Just don't know how to simplify
    it.
    I have previously been asked to post my questions in a certain way.
    Unfortunately for a novice, this is not always possible. Please excuse
    my way of asking this, I think, at least for laymen, this is the simplest
    way to pose the scenario.

    Here are a set of records with the most important columns represented.

    Competitor Name | DivNum | DivDesc | DivRank |
    PointsThisDay | Tournament

    Mia Tinkler | 213 | Forms | Black
    Belt | 100 | Gold Coast Open
    Don T. B. Me | 106 | Weapons | Advanced |
    400 | Gold Coast Open
    Gobe Faraway | 106 | Weapons | Advacned |
    300 | Gold Coast Open
    Idone Gottabeamer | 213 | Forms | Black Belt |
    400 | Gold Coast Open
    Mia Tinkler | 310 | Sparring | Black
    Belt | 300 | Gold Coast Open
    Ted N. Alice | 310 | Sparring | Black
    Belt | 400 | Gold Coast Open
    Mia Tinkler | 213 | Forms | Black
    Belt | 300 | Battle By The Beach
    Don T. B. Me | 106 | Weapons | Advanced |
    200 | Battle By The Beach
    Gobe Faraway | 106 | Weapons | Advacned |
    300 | Battle By The Beach
    Idone Gottabeamer | 213 | Forms | Black Belt
    | 500 | Battle By The Beach
    Mia Tinkler | 310 | Sparring | Black
    Belt | 400 | Battle By The Beach
    Ted N. Alice | 310 | Sparring | Black
    Belt | 500 | Battle By The Beach

    There are thousands of these entires. Each competitor can be in several
    DivNum and Tournaments, their DivRank will
    be consistent. Their Points will vary. I need to QUERY any single
    competitor's points total in each of their DivDesc and DivRank,
    compare that with everyone else in that DivDesc and DivRank, respectively,
    and my result should be that competitor's relative standing
    in each of their DivDesc and DivRank. By totaling all competitors in any
    single DivDesc and DivRank(regardless of tournament) and
    then comparing their total points against the others in that DivDesc and
    DivRank, I can assign that competitor a placement from 1st
    thru whatever (there can be ties for a postion). For clarification, I need
    all (DivDesc and DivRank) that one specific competitor has
    competed in, to be a result, each as a separate record in the resultant
    recordset.

    If someone can get me started on some kind of flow here, I would surely
    appreciate it. This type of query is beyond my
    knowledge at this time. Pointing me in the right direction will surely help,
    but I will more than likely have
    follow-up questions on syntax. I am guessing that once I understand this, I
    will have learned a lot for the future.

    Thanks in advance to anyone who is willing to give this some time.
    Fox


    Fox Guest

  2. #2

    Default Re: Requesting help with complicated query


    "Fox" <fox connexions .net> wrote in message
    news:#2bHI$MRDHA.2144TK2MSFTNGP11.phx.gbl...
    > Hi,
    >
    > Sorry, for the lack of info in the title. Just don't know how to simplify
    > it.
    > I have previously been asked to post my questions in a certain way.
    > Unfortunately for a novice, this is not always possible. Please excuse
    > my way of asking this, I think, at least for laymen, this is the simplest
    > way to pose the scenario.
    >
    > Here are a set of records with the most important columns represented.
    >
    > Competitor Name | DivNum | DivDesc | DivRank |
    > PointsThisDay | Tournament
    >
    > Mia Tinkler | 213 | Forms | Black
    > Belt | 100 | Gold Coast Open
    > Don T. B. Me | 106 | Weapons | Advanced |
    > 400 | Gold Coast Open
    > Gobe Faraway | 106 | Weapons | Advacned |
    > 300 | Gold Coast Open
    > Idone Gottabeamer | 213 | Forms | Black Belt
    |
    > 400 | Gold Coast Open
    > Mia Tinkler | 310 | Sparring | Black
    > Belt | 300 | Gold Coast Open
    > Ted N. Alice | 310 | Sparring | Black
    > Belt | 400 | Gold Coast Open
    > Mia Tinkler | 213 | Forms | Black
    > Belt | 300 | Battle By The Beach
    > Don T. B. Me | 106 | Weapons | Advanced
    |
    > 200 | Battle By The Beach
    > Gobe Faraway | 106 | Weapons | Advacned |
    > 300 | Battle By The Beach
    > Idone Gottabeamer | 213 | Forms | Black Belt
    > | 500 | Battle By The Beach
    > Mia Tinkler | 310 | Sparring | Black
    > Belt | 400 | Battle By The Beach
    > Ted N. Alice | 310 | Sparring | Black
    > Belt | 500 | Battle By The Beach
    >
    > There are thousands of these entires. Each competitor can be in several
    > DivNum and Tournaments, their DivRank will
    > be consistent. Their Points will vary. I need to QUERY any single
    > competitor's points total in each of their DivDesc and DivRank,
    > compare that with everyone else in that DivDesc and DivRank, respectively,
    > and my result should be that competitor's relative standing
    > in each of their DivDesc and DivRank. By totaling all competitors in any
    > single DivDesc and DivRank(regardless of tournament) and
    > then comparing their total points against the others in that DivDesc and
    > DivRank, I can assign that competitor a placement from 1st
    > thru whatever (there can be ties for a postion). For clarification, I need
    > all (DivDesc and DivRank) that one specific competitor has
    > competed in, to be a result, each as a separate record in the resultant
    > recordset.
    >
    > If someone can get me started on some kind of flow here, I would surely
    > appreciate it. This type of query is beyond my
    > knowledge at this time. Pointing me in the right direction will surely
    help,
    > but I will more than likely have
    > follow-up questions on syntax. I am guessing that once I understand this,
    I
    > will have learned a lot for the future.
    >
    > Thanks in advance to anyone who is willing to give this some time.
    > Fox
    >
    First question is why did this all word-wrap ? Should I repost ?
    I should have added that I expect I will be summing PointsThisDay
    and grouping (DivDesc and DivRank). But, I don't know how to perform
    the comparisons to all others and to end up with a WHERE or HAVING
    CompetitorName be just the one I am querying for.

    Thanks again,
    Fox


    Fox Guest

  3. #3

    Default Re: Requesting help with complicated query


    "Fox" <fox connexions .net> wrote in message
    news:%232bHI$MRDHA.2144TK2MSFTNGP11.phx.gbl...
    >
    > If someone can get me started on some kind of flow here, I would surely
    > appreciate it. This type of query is beyond my
    > knowledge at this time. Pointing me in the right direction will surely
    help,
    > but I will more than likely have
    > follow-up questions on syntax. I am guessing that once I understand this,
    I
    > will have learned a lot for the future.
    >
    > Thanks in advance to anyone who is willing to give this some time.
    > Fox
    >
    I think this meets your requirements.
    Sometimes breaking the query down into steps using temp tables can help.
    This is pretty much the SQL translation of what you wrote in English. In
    other words, you seem to have the wording of the algorithm solved already!

    create table Results(
    name varchar(32),
    divnum smallint,
    divDesc varchar(16),
    divRank varchar(16),
    Points smallint,
    tournament varchar(32)
    )
    GO

    insert results
    select
    'Mia Tinkler' , 213 , 'Forms' , 'Black Belt' , 100 , 'Gold Coast Open' union
    select
    'Don T. B. Me' , 106 , 'Weapons' , 'Advanced' , 400 , 'Gold Coast Open'
    union select
    'Gobe Faraway' , 106 , 'Weapons' , 'Advacned' , 300 , 'Gold Coast Open'
    union select
    'Idone Gottabeamer' , 213 , 'Forms' , 'Black Belt' , 400 , 'Gold Coast Open'
    union select
    'Mia Tinkler' , 310 , 'Sparring' , 'Black Belt' , 300 , 'Gold Coast Open'
    union select
    'Ted N. Alice' , 310 , 'Sparring' , 'Black Belt' , 400 ,'Gold Coast Open'
    union select
    'Mia Tinkler' , 213 , 'Forms' , 'Black Belt' , 300 , 'Battle By The Beach'
    union select
    'Don T. B. Me' , 106 , 'Weapons','Advanced' , 200 , 'Battle By The Beach'
    union select
    'Gobe Faraway' , 106 , 'Weapons','Advacned' , 300 , 'Battle By The Beach'
    union select
    'Idone Gottabeamer' , 213 , 'Forms' ,'Black Belt', 500 , 'Battle By The
    Beach' union select
    'Mia Tinkler' , 310 , 'Sparring' ,'Black Belt' , 400 , 'Battle By The Beach'
    union select
    'Ted N. Alice' , 310 , 'Sparring' ,'Black Belt' , 500 , 'Battle By The
    Beach'

    select
    name,
    divnum,
    divdesc,
    divrank,
    sum(points) as totalpoints
    into #tmp
    from
    results
    group by
    name,
    divnum,
    divdesc,
    divrank


    select
    t.name,
    t.divnum,
    t.divdesc,
    t.divrank,
    t.totalpoints,
    (select
    count(*) + 1
    from
    #tmp
    where
    divdesc = t.divdesc and
    divrank = t.divrank and
    divnum = t.divnum and
    totalPoints > t.totalpoints
    ) as rank
    from #tmp t
    order by divnum, divdesc, divrank, rank asc


    Donald Halloran Guest

Similar Threads

  1. Replies: 1
    Last Post: June 26th, 04:09 PM
  2. Requesting web page from SSL site fails
    By Tim Mavers in forum ASP.NET Security
    Replies: 3
    Last Post: January 13th, 01:23 AM
  3. Requesting Variables
    By Ray at in forum ASP
    Replies: 0
    Last Post: September 2nd, 04:05 PM
  4. Requesting URL
    By David de Jong in forum ASP
    Replies: 3
    Last Post: August 14th, 01:30 PM
  5. How to tell what process is requesting dial-up
    By Bob in forum Windows Networking
    Replies: 0
    Last Post: July 10th, 03:21 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