Picking out top three out of database

Discussion in 'ASP General' started by Thomas, Dec 8, 2004.

  1. Thomas

    Thomas Guest

    I have a database where I store info about football players goals. The
    database contains the following:
    GoalID, GoalScorerID, GoalMinute (which minute the goal was scored),
    ClubID, FixtureID (to mark in which match the goal was scored) and
    Penalty (which determines wether the goal was a penalty or not).

    On the club page I want to show the top three goalscorers of the club.
    I can do this but I don´t know how to get them in descending order
    with the player who´s scored most goals at the top of the list.

    Any ideas?
    Thomas, Dec 8, 2004
    #1
    1. Advertising

  2. SELECT TOP 3
    and
    ORDER BY player DESC or ASC
    Gérard Leclercq, Dec 8, 2004
    #2
    1. Advertising

  3. Thomas

    Jeff Cochran Guest

    On 8 Dec 2004 00:23:02 -0800, (Thomas)
    wrote:

    >I have a database where I store info about football players goals. The
    >database contains the following:
    >GoalID, GoalScorerID, GoalMinute (which minute the goal was scored),
    >ClubID, FixtureID (to mark in which match the goal was scored) and
    >Penalty (which determines wether the goal was a penalty or not).
    >
    >On the club page I want to show the top three goalscorers of the club.
    >I can do this but I don´t know how to get them in descending order
    >with the player who´s scored most goals at the top of the list.
    >
    >Any ideas?


    Lots, some rather simple. Some may not apply to your database though.
    If you tell us what database, we can suggest a solution.

    (Hint: Check to see if your particular database supports the TOP
    modifier in a SELECT statement...)

    Jeff
    Jeff Cochran, Dec 8, 2004
    #3
  4. Sorry for not answering earlier but it´s been a bit much to do.

    The db used is Access. I have no clue if it supports the TOP modifier.
    If not Access is to be used which db should I use. I need it to be free.



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Thomas Emilson, Dec 15, 2004
    #4
  5. Thomas Emilson wrote:
    > Sorry for not answering earlier but it´s been a bit much to do.
    >
    > The db used is Access. I have no clue if it supports the TOP modifier.
    > If not Access is to be used which db should I use. I need it to be
    > free.
    >
    >
    >

    TOP works fine in Access. In the future, specify your database type and
    version right at the start of your post so we dont' have to ask.
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Dec 15, 2004
    #5
  6. Thomas

    Thomas Guest

    Yeah, I´ll think of that next time around, but back to the thing here.

    So how one use this TOP modifier?
    I´m not very good at sql so if anyone who feels they are would like to
    explain this to me I would be grateful.

    /Thomas

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Thomas, Dec 15, 2004
    #6
  7. Thomas wrote:
    > Yeah, I´ll think of that next time around, but back to the thing here.
    >
    > So how one use this TOP modifier?
    > I´m not very good at sql so if anyone who feels they are would like to
    > explain this to me I would be grateful.
    >

    Well, Access DOES have online help. It also has a dandy Query Builder that
    generates the SQL for you.


    Basically, the syntax is
    SELECT TOP x <column list> FROM <table/query>

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Dec 15, 2004
    #7
  8. Thomas

    dlbjr Guest

    It does support TOP

    --
    dlbjr
    Pleading sagacious indoctrination!
    dlbjr, Dec 16, 2004
    #8
  9. Thomas

    Thomas Guest

    Hi again Bob!

    I´ve been at the online help for access and looked at the top modifier
    but I couldn´t find out how it could help me.
    It could only get the top posts of the recordset based on the value of
    one of the columns.

    But my problem is that I don´t have a column (or a table) that contains
    the total goals scored. In my table 'Goalscorers' I have all the goals
    that has been done and in which minute it was scored (and by which
    player). Then I do a For... exit to find out how many times a playerid
    is found in the table (which gives me the number of goals he has
    scored).

    The table looks like this:

    GoalID | GoalScorerID | GoalMinute | ClubID | FixtureID


    Hope this helps anything.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Thomas, Dec 16, 2004
    #9
  10. Thomas wrote:
    > Hi again Bob!
    >
    > I´ve been at the online help for access and looked at the top modifier
    > but I couldn´t find out how it could help me.
    > It could only get the top posts of the recordset based on the value of
    > one of the columns.
    >
    > But my problem is that I don´t have a column (or a table) that
    > contains the total goals scored. In my table 'Goalscorers' I have all
    > the goals that has been done and in which minute it was scored (and
    > by which player). Then I do a For... exit to find out how many times
    > a playerid is found in the table (which gives me the number of goals
    > he has scored).
    >
    > The table looks like this:
    >
    > GoalID | GoalScorerID | GoalMinute | ClubID | FixtureID
    >
    >
    > Hope this helps anything.


    So now you need to look up grouping and aggregate functions to, well, group
    and aggregate the goals scored by each player.

    SELECT TOP 3 GoalScorerID, Count(*) As TotalGoals
    FROM GoalScorers
    GROUP BY GoalScorerID
    ORDER BY TotalGoals DESC

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Dec 16, 2004
    #10
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Frederic HOUDE
    Replies:
    4
    Views:
    586
    Frederic HOUDE
    Aug 31, 2004
  2. Replies:
    1
    Views:
    834
    Larry I Smith
    Jun 12, 2005
  3. Cuthbert
    Replies:
    8
    Views:
    420
    Ancient_Hacker
    Sep 13, 2006
  4. AAaron123
    Replies:
    6
    Views:
    557
    AAaron123
    Nov 5, 2009
  5. Thomas

    Picking out top three out of database

    Thomas, Dec 14, 2004, in forum: ASP General
    Replies:
    1
    Views:
    96
    s_m_b
    Dec 14, 2004
Loading...

Share This Page