Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    2

    Unanswered: Limit number of records returned by SQL Query

    I am designing a database for a fishing club to keep track of the fishing competition scores (in MS Access XP).

    I have three tables; Catch (containing date, boat, fish, fish weight), Boats (list of boats), Fish (list of fish types and how many points they are worth). The score is determined as follows; each species of fish is worth a different score (per 100g), the score for each boat is determined as the sum of the top 5 scoring fish for each species. i.e. the score for Boat1 is determined from the five heaviest flounder, snapper, crayfish, etc.

    I have written the following query to do this:

    Code:
    SELECT Boats.Boatname, Catch.Date, Fish.Species, Catch.Weight, Fish.[Weighted points], [Weighted points]*[weight] AS [Points Awarded]
    FROM Boats INNER JOIN (Fish INNER JOIN Catch ON Fish.ID = Catch.Fish) ON Boats.ID = Catch.Boat 
    WHERE (((Catch.Weight) In (SELECT TOP 5 Catch.Weight From Catch WHERE Catch.Fish = Fish.ID AND Boats.ID = Catch.Boat ORDER BY Catch.Weight DESC)))
    ORDER BY Boats.Boatname, Fish.Species, Catch.Weight DESC;
    The query works fine except when there are several fish with the same weight that are tied for fifth position. The TOP query returns all ties in Access SQL so I end up with six or seven fish returned which give a high score. I need to return only 5 fish even if there is a tie.

    This seems to be possible in Oracle and Access Server using rownum or limit functions but these are not available in Access XP. Is there a way to limit the number of records returned by the subquery in Access XP?

  2. #2
    Join Date
    Dec 2004
    Posts
    2

    Limit number of records returned by SQL Query

    Any suggestions?

  3. #3
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    In the case of ties, how do you determine who wins? What if you have 6 contestants with the same size fish? Which 5 of the 6 get the prizes? How do you determine that? Do you have a good lawyer for the inevitable lawsuit from the 6th man out???
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

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