Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Picking out top three out of database

Reply
Thread Tools

Picking out top three out of database

 
 
Thomas
Guest
Posts: n/a
 
      12-08-2004
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?
 
Reply With Quote
 
 
 
 
Gérard Leclercq
Guest
Posts: n/a
 
      12-08-2004
SELECT TOP 3
and
ORDER BY player DESC or ASC


 
Reply With Quote
 
 
 
 
Jeff Cochran
Guest
Posts: n/a
 
      12-08-2004
On 8 Dec 2004 00:23:02 -0800, http://www.velocityreviews.com/forums/(E-Mail Removed) (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
 
Reply With Quote
 
Thomas Emilson
Guest
Posts: n/a
 
      12-15-2004
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!
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      12-15-2004
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"


 
Reply With Quote
 
Thomas
Guest
Posts: n/a
 
      12-15-2004


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!
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      12-15-2004
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"


 
Reply With Quote
 
dlbjr
Guest
Posts: n/a
 
      12-16-2004
It does support TOP

--
dlbjr
Pleading sagacious indoctrination!


 
Reply With Quote
 
Thomas
Guest
Posts: n/a
 
      12-16-2004
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!
 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      12-16-2004
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"


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Database Database Database Database scott93727@gmail.com Computer Information 0 09-27-2012 02:43 AM
DataBase DataBase DataBase DataBase scott93727@gmail.com Computer Information 0 09-26-2012 09:40 AM
Re: Three Mobile --> Skype on three (Non-three [Symbian - Nokia] handsets) Harry Stottle UK VOIP 0 01-05-2010 08:59 AM
Picking out top three out of database Thomas ASP General 1 12-14-2004 04:00 PM
Same icons three different areas on desk top °Mike° Computer Support 5 10-08-2003 10:19 PM



Advertisments