SQL for a scoreboard

Hi,

My loathe for MS SQL is growing by the minute, maybe someone can help.

OK it's a scoreboard scenario. (Using ASP / Access DB)

There are 6 fields we're interested in found in a table called Records

ID - autonum
Alias - string
DateTime - date/time
LapTime - long integer
TrackID - integer
CraftID - integer


I want the sql to:

1. return the results from the table where TrackID = 1 and CraftID = 0.
2. order the results by LapTime ascending.
3. return only the best result per unique Alias. (there's several laptimes per alias, and many aliases, obviously). The best can be defined as the lowest LapTime.

Apply those rules to the same query (e.g. I don't want separates)

I've come up with so many statements, they either fall over in some way or I get a niggly error. Some help here would really be appreciated.

Sounds 'easy' doesn't it?
It probably is, but not for me anyway. This 'language' is probably the worst one in existance, and it shouldn't be.

Dan

this is easy, if you know about correlated subqueries, which, i guess, is not always the first thing you learn, and definitiely pretty hard to figure out on your own

Hi there mate,

I have to thank you really for your help, your query was missing the TrackSum and CraftID conditions from the WHERE clause, other than that it worked PERFECTLY.


THANKS once again my friend

Dan


you can check it out at URL