How to find most expensive items in sql with more than one most expensive item?

Question

I have more than one items with the highest price on my database and I would like to know how to return the most expensive items with the highest price on my database, without using LIMIT. I have tried using

SELECT MAX(price) FROM items

but it only returns one item with the highest price (I have two items with the highest price).

The schema of my database is

items (itemID: integer, description: string, price: integer)

Solution 1

One way would be to use a join to fetch them all like so:

SELECT I.* FROM Items I JOIN
   (SELECT MAX(price) AS maxprice FROM items) M
   ON I.price=M.maxprice;

Solution 2

This will work.

SET @p1 := (SELECT MAX(price) FROM items);
SELECT * FROM items WHERE price = @p1;

Using variables, p1 stores the maximum price from the table items and then uses the variable p1 in the following query to return all records which have that maximum price without limiting the number of records as you desired.