SEO

Hi. I'm stumped and came here for help. After poking around I realize
you guys know way more than me. Still, a little help would be much
appreciated.

I've got a table with columns ClassNumber, TheDate, RaceNumber,
StudentID, RaceSpeed.

There are race results for this year's classes. My goal is to create a
report / query that shows the race winner for each race in the table.
My plan was to do this by selecting the identifying information using a
Min(RaceSpeed).

The problem I'm facing is that I'm not sure how to setup this "group" or
even if it is possible. A unique race is made up of the fields
ClassNumber, TheDate, and RaceNumber so I would need to group on
multiple fields to make it happen.

Is this possible?

Perhaps I setup the table incorrectly that would make this type of
select impossible. I really have no idea.

Please help.

Thank you.

-Scott

On Tue, 29 Apr 2008 04:23:45 +0200, scott <scott@email.com> wrote:

> Hi. I'm stumped and came here for help. After poking around I realize
> you guys know way more than me. Still, a little help would be much
> appreciated.
>
> I've got a table with columns ClassNumber, TheDate, RaceNumber,
> StudentID, RaceSpeed.
>
> There are race results for this year's classes. My goal is to create a
> report / query that shows the race winner for each race in the table. My
> plan was to do this by selecting the identifying information using a
> Min(RaceSpeed).
>
> The problem I'm facing is that I'm not sure how to setup this "group" or
> even if it is possible. A unique race is made up of the fields
> ClassNumber, TheDate, and RaceNumber so I would need to group on
> multiple fields to make it happen.
>
> Is this possible?

SELECT
x.ClassNumber,x.TheDate,x.RaceNumber,x.studentId,x .RaceSpeed
FROM tablename x
LEFT JOIN tablename y
#JOIN on the same race:
ON x.ClassNumber = y.ClassNumber
AND x.TheDate = y.TheDate
AND x.RaceNumber = y.RaceNumber
#Where racespeed is smaller:
AND y.RaceSpeed < x.RaceSpeed
#where there is no smaller racespeed:
WHERE y.StudentId IS NULL

> Perhaps I setup the table incorrectly that would make this type of
> select impossible. I really have no idea.

Preferable would to have a 'races' table with some unique id, and a
'results' table referencing that races table, with a racespeed result &
student id.
--
Rik Wasmus