| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 52726 | 2004-12-28 05:37:00 | Top 5 SQL Query | Alpha (3520) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 308167 | 2004-12-28 05:37:00 | I have been asked by a friend to design an access database for the fishing club to keep track of the fishing competition scores. 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: 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. My other thought was to the RowNum function but this is only available on Server editions/T-SQL. Any suggestions? |
Alpha (3520) | ||
| 308168 | 2004-12-28 07:36:00 | Worth trying ....SELECT DISTINCT TOP 5 Catch.Weight.... Don |
donread (6401) | ||
| 308169 | 2004-12-28 08:28:00 | Thanks Don, DISTINCT doesn't seem to have any affect when placed into the sub query. If I place DISTINCT at the beginning of the main query, all duplicates are removed, including those that are tied for second or third (which I want to keep). i.e. The set [10, 9, 9, 7, 5, 5, 3] should become [10, 9, 9, 7, 5]. |
Alpha (3520) | ||
| 308170 | 2004-12-28 09:41:00 | Are you accessing this via a webpage, or just talking raw SQL to the database? If you're doing it via a web-site (ASP/PHP) you can limit the results using a script in the web code rather than the SQL. Brutal hack, but unfortunately MS-SQL doesn't seem to have an equivalent to the LIMIT of My_SQL. |
ninja (1671) | ||
| 308171 | 2004-12-28 10:16:00 | Mmmm, I'm just a bunny at Access yet, and if you wrote the SQL, you know more than me, but it seems to me you may need to rethink the method? eg. it seems concievable there were five 9s or three 7s? Could probably do it with 5 Queries and a Form storing the results to be used as parameters. Recomend you look here, well worth subscribing to, some real Guns peach.ease.lsoft.com Hope it helps Don |
donread (6401) | ||
| 308172 | 2004-12-28 10:39:00 | if I remember my access correctly the imbeded query is run first. Therefore if I am correct the query that selects the five top records should be first in the sequence because at the moment that query has no data to act on as the first query has not run ( the way you have consructed the the sql). simply put, change the order in which the queries are run in your example |
beama (111) | ||
| 308173 | 2004-12-28 20:15:00 | Thanks for the suggestions. I am using Access XP to write the database, results of the query are sent to an access report. As I am trying to limit the results returned by the subquery I am not sure limiting the report results would work. I will look at the link provided by Donread, looks like I have a lot of reading to do! (archives go back to 1993) I thought the subquery ran second Beama? The TOP query needs to be embeded so that I can get the top five for each boat. If I swap it around I get the top five fish between all boats. The query that I implemented was based on a tutorial I found in Database Journal (www.databasejournal.com), this seems to work OK in the perfect world when there are no ties (but we all know the world is not perfect). |
Alpha (3520) | ||
| 308174 | 2004-12-28 22:57:00 | Definately worth searching their achives, but post your question to the List, I for one wil be interested in the solution. Don |
donread (6401) | ||
| 308175 | 2004-12-28 23:59:00 | Supplying an additional sort criteria to the TOP 5 query might solve the problem. The primary key on the catch table should work. SELECT TOP 5 Catch.Weight From Catch WHERE Catch.Fish = Fish.ID AND Boats.ID = Catch.Boat ORDER BY Catch.Weight, Catch.ID DESC |
bmason (508) | ||
| 308176 | 2004-12-29 01:24:00 | Almost... When I run the modified TOP 5 query (ORDER BY Catch.Weight, Catch.ID DESC) without nesting it into the main query I get exactly 5 results returned. Yahoo! :) But if I insert it back into the main query (so the TOP 5 query is repeated for each boat and fish) I get the ties appearing again. Strange. :confused: | Alpha (3520) | ||
| 1 | |||||