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

43 Highest and lowest Table

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.

44 Desc limit Query

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.

45 Desc results

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.

46 ASC limit results

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.