MTB Rider done last 5 races - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

MTB Rider done last 5 races

Author

Topic

Scott
Posting Yak Master

145 Posts

Posted - 2002-11-29 : 08:54:33

I've got 2 tables; Riders & Challenge

Riders contains each riders name and personal info and is indexed on a unique ID - RiderID
Challenge contains the history so there is a one to many relationship on the RiderID.

I want a list of all those who have ridden the last 5 challenges i.e. 1998-2002 (consecutive)

???

Edited by - scott on 11/29/2002 09:01:08

SamC
White Water Yakist

3467 Posts

Posted - 2002-11-29 : 09:13:59

Hi Scott,

I'll give it a shot, but you should post a short table structure. There's no mention of a date field, rider name or challenge name to identify the last 5 in the query recordset. I'm sure you want more than the RiderID returned.

SELECT Ridername, ChallengeName, Challengedate
FROM Riders R
INNER JOIN Challenge C ON R.RiderID=C.RiderID
WHERE Challengedate BETWEEN 'Jan 1, 1998' AND 'Jan 1, 2003'
ORDER BY Challengedate, ChallengeName, RiderName

This query has three columns I've had to make up lacking the correct table structure.

I think this query meets your needs. What would be interesting would be to modify the query to get the last 5, no matter what the dates are.

Sam

nr
SQLTeam MVY

12543 Posts

Posted - 2002-11-29 : 09:24:14

You want those that have ridden in them all?

SELECT Ridername
FROM Riders R
INNER JOIN Challenge C ON R.RiderID=C.RiderID
WHERE Challengedate BETWEEN 'Jan 1, 1998' AND 'Jan 1, 2003'
group by Ridername
having count(*) = 5

for those riding any event just miss out the having clause

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources