Picking out top three out of database

[Thomas]

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?

[Gérard Leclercq]

SELECT TOP 3
and
ORDER BY player DESC or ASC

[Jeff Cochran]

On 8 Dec 2004 00:23:02 -0800, th***********@hotmail.com (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

[Thomas Emilson]

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!

[Bob Barrows [MVP]]

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"

[Thomas]

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!

[Bob Barrows [MVP]]

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"

[dlbjr]

It does support TOP

--
dlbjr
Pleading sagacious indoctrination!

[Thomas]

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!

[Bob Barrows [MVP]]

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"