Dear All, I have a table contains airline ticket prices, and I want to get the following data from this table The first 5 records of the lowest price of each airline from each city, and don't want to get a duplicated combination of (airline, city) in this result (even if I didn't see all airlines or all cities, I want to see only five records). I get the required results (in MSAccess) by doing the following: 1- Create a query "Q1" with the following source: SELECT distinct [ticket cut rate].[primary airline] FROM [ticket cut rate] 2- Then Create a second Query "Q2" with the following source: SELECT Q1.[primary airline], Min([ticket cut rate].[price per adult]) AS [MinOfprice per adult] FROM Q1 INNER JOIN [ticket cut rate] ON Q1.[primary airline] = [ticket cut rate].[primary airline] GROUP BY Q1.[primary airline];
3- And I get the required results from the following Query: SELECT TOP 5 First([ticket cut rate].origin) AS FirstOforigin, [ticket cut rate].[primary airline], [ticket cut rate].[price per adult] FROM (Q1 INNER JOIN Q2 ON Q1.[primary airline] = Q2.[primary airline]) INNER JOIN [ticket cut rate] ON (Q2.[primary airline] = [ticket cut rate].[primary airline]) AND (Q2.[MinOfprice per adult] = [ticket cut rate].[price per adult]) GROUP BY [ticket cut rate].[primary airline], [ticket cut rate].[price per adult]; How can I get the same result with only one sql statement??? Oh by the way, can I create queries in MySQL just like MSAccess???? Please help bst rgds Hayan --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
