Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    3

    Unanswered: Query help... TOP 5,

    Hi i am new to this SQL stuff, as before have been able to the simple things in MS Access design view.
    But i am having troubles acheiving what i need.
    The situation is i have a database for horse racing stats.
    There is a few tables, the ones that are related to this are
    1. raceList - has raceId, date ... - a list of races
    2. raceStarters - raceId, horseId ... - a list of horses in each race
    3. previousForm - horseId, date etc...

    what i want to be able to do is have a query that for a particular raceId, will display the last 5 previousForm records (sorted by date DESC), for each horseId, in that raceId.

    Hope this is clear and someone can help...

    Thanks,
    Blocka

  2. #2
    Join Date
    Jan 2003
    Location
    India
    Posts
    523

    Re: Query help... TOP 5,

    I hope this is what u want:

    select top 5 * from previousForm where horseid in (select horseid from racestarters where raceid=@raceid )
    order by date desc



    replace the @raceid with the actual raceid.


    regards,
    Harshal.

  3. #3
    Join Date
    Apr 2004
    Posts
    3
    thanks harshal_in, but it doesn't quite do what i wanted.
    The query you have given returns only a total of 5 previousForm results for all horseId's.
    What i need is that it will return 5 previousForm for each horseId. So that if there are 8 horseId in a raceId the query will return 40 previousForm results.
    Cheers,
    Blocka..

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is a shot in the dark (I haven't tried it), but:
    PHP Code:
    SELECT *
       
    FROM previousForm AS a
       WHERE  a
    .date IN (SELECT TOP 5 b.date
          FROM previousForm 
    AS b
          WHERE  b
    .horseId a.horseId
             
    AND EXISTS (SELECT *
                
    FROM raceStarters AS c
                WHERE  c
    .horseId b.horseId
                   
    AND c.raceId = @raceId)
          
    ORDER BY b.date DESC
    -PatP

  5. #5
    Join Date
    Apr 2004
    Posts
    3
    Thanks Pat that does indeed seem to work from a quick try. Now i just have to try and understand your query.
    Thanks again.

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The indentation is for a reason... The further right a line begins, the deeper it is logically nested. Start at the innermost stuff and work your way out and it shouldn't be too bad.

    The SELECT aliased as c finds the horses that started in a given race. The SELECT aliased as b finds the most recent 5 dates for a given horse. The SELECT aliased as a is the one that actually produces the rows that go into the result set.

    -PatP

Posting Permissions

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