To retrieve the top 5 highest and lowest values from a table in SQL, you typically need to use the ORDER BY clause along with the LIMIT clause (in databases like MySQL, PostgreSQL, etc.). However, to do this in a single query, you can use a UNION to combine the results. Below are examples for both the highest and lowest values:
Assuming your table is named your_table and the column you want to get the highest and lowest values from is value_column.
Query to retrieve the top 5 highest values:
SELECT value_column
FROM your_table
ORDER BY value_column DESC
LIMIT 5;
Query to retrieve the top 5 lowest values:
SELECT value_column
FROM your_table
ORDER BY value_column ASC
LIMIT 5;
Combined Query Using UNION
If you want to retrieve both the highest and lowest values in a single query result set, you can use a UNION:
(
SELECT value_column, 'Highest' AS category
FROM your_table
ORDER BY value_column DESC
LIMIT 5
)
UNION ALL
(
SELECT value_column, 'Lowest' AS category
FROM your_table
ORDER BY value_column ASC
LIMIT 5
)
ORDER BY category, value_column;
Explanation:
- The UNION ALL combines the results of the two queries.
- Each query specifies a category (either 'Highest' or 'Lowest') to distinguish between the two sets of results.
- The final ORDER BY category, value_column ensures that you can see both categories clearly, although you might want to change the order to fit your needs.
Make sure to adjust your_table and value_column to match the actual names used in your database.