Help Deriving SQL statement...

Results 1 to 2 of 2

Thread: Help Deriving SQL statement...

  1. #1
    Join Date
    Dec 1969

    Default Help Deriving SQL statement...

    Hello. I need some assistance in deriving an SQL statement. My situation is this:<BR><BR>I have a database with two tables. One is called Horses, which has a Primary key field and a field called Horses Name. There is another table called Races. Which has an RNumber field (to form a Parent-Child relationship), and fields to track the Race history of the horses (e.g. Date, Track Name, etc.)<BR><BR>What I need to do is, for each Horse Name, I need to pull just the one NEWEST record for that horses race. Can someone tell me how to build an SQL statement to accomplish that? It would be greatly appreciated. THank you all!

  2. #2
    Join Date
    Dec 1969

    Default RE: Help Deriving SQL statement...

    Heya, <BR><BR>You might try something like this, which assumes both your PK & FK columns are called HorseID:<BR><BR>SELECT h.HorseName,r.TrackName FROM Horses h INNER JOIN Race r ON h.HorseID = (SELECT TOP 1 r2.HorseID FROM Race r2 WHERE r2.HorseID=h.HorseID ORDER BY Date DESC)<BR><BR>Or something along those lines.<BR><BR>Hth, <BR><BR>])ry<BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts