Advanced Database – Show highest & lowest range of values in a query.
First, we are going to try and display the 3 highest priced jobs.
Create a new query and add the table Job Costs
In the first column display all fields *. In the second column choose the Cost field and select ‘descending’ in the Sort box.
Press F5 to run the query and notice that it displays all of the jobs in descending order of cost.
In order to show only the top 3 records, switch into SQL view.
It will already have the query we created in design mode i.e: SELECT * FROM “Job Costs” ORDER BY “Cost” DESC
Type immediately after DESC: LIMIT 3
This will limit the results to only 3 records. Press F5 to run the query again.
This time it only displays the highest 3 costs, as required.
If instead we want to show the jobs with the lowest 5 costs, we can edit the SQL again.
Change the SQL to: SELECT * FROM “Job Costs” ORDER BY “Cost” ASC LIMIT 5
Press F5 to run the query.
his time it displays the records with the lowest 5 prices.
