Schema and sample data
| product_id | name | category | price |
|---|---|---|---|
| 1 | Alpha Keyboard | Accessories | 45.00 |
| 2 | Bravo Monitor | Displays | 320.00 |
| 3 | Charlie Laptop | Computers | 1200.00 |
| 4 | Delta Mouse | Accessories | 25.00 |
| 5 | Echo Tablet | Computers | 650.00 |
| 6 | Foxtrot Webcam | Accessories | 89.00 |
| 7 | Golf Docking Station | Accessories | 180.00 |
Show setup SQL (copy to run)
CREATE TABLE Products (
product_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
category VARCHAR(30),
price DECIMAL(10,2) NOT NULL
);
INSERT INTO Products (product_id, name, category, price) VALUES
(1, 'Alpha Keyboard', 'Accessories', 45.00),
(2, 'Bravo Monitor', 'Displays', 320.00),
(3, 'Charlie Laptop', 'Computers', 1200.00),
(4, 'Delta Mouse', 'Accessories', 25.00),
(5, 'Echo Tablet', 'Computers', 650.00),
(6, 'Foxtrot Webcam', 'Accessories', 89.00),
(7, 'Golf Docking Station', 'Accessories', 180.00);From the Products table, return the name and price of the 5 most expensive products. Sort the result by price from highest to lowest, so the priciest product appears first.
Expected result
| name | price |
|---|---|
| Charlie Laptop | 1200.00 |
| Echo Tablet | 650.00 |
| Bravo Monitor | 320.00 |
| Golf Docking Station | 180.00 |
| Foxtrot Webcam | 89.00 |
Show hint
You do not need a WHERE clause here. Sort every row by price from highest to lowest, then keep only the first few rows. In SQL Server that row limit goes right after SELECT.
Try to write the query yourself before you open this.
Show solution and explanation
SELECT TOP 5 name, price
FROM Products
ORDER BY price DESC;ORDER BY price DESC sorts every product from the most expensive down to the cheapest, and TOP 5 then keeps only the first 5 rows of that sorted output. The order matters: SQL Server applies TOP after ORDER BY, so the sort decides which 5 rows survive. Charlie Laptop (1200.00) leads, followed by Echo Tablet, Bravo Monitor, Golf Docking Station and Foxtrot Webcam, leaving Alpha Keyboard and Delta Mouse out.
TOP is the classic SQL Server way to cap a result set. The standard alternative is ORDER BY price DESC OFFSET 0 ROWS FETCH NEXT 5 ROWS ONLY, which does the same job and, unlike TOP, lets you skip rows with OFFSET for paging. Note that other databases use LIMIT instead, but that keyword is not valid T-SQL.
One thing to watch is ties. Plain TOP 5 returns exactly 5 rows even if the fifth and sixth products share the same price, and which of the tied rows you get is arbitrary. If you want to include every row that ties with the fifth one, write SELECT TOP 5 WITH TIES ... ORDER BY price DESC, which can return more than 5 rows. Here all prices are distinct, so both forms give the same 5 rows.