How can I find the most expensive product in a Product table without using MAX, LIMIT, or ORDER BY i-CodePudding

I'm supposed to find it without using those, and I can't think of anything and haven't found any information. Thanks

CodePudding user response：

Use NOT EXISTS:

SELECT *
FROM   product p
WHERE  NOT EXISTS (
  SELECT 1
  FROM   product x
  WHERE  p.price < x.price
)

CodePudding user response：

You can do a self outer join on the price being lesser. Supposing you have a table products(id int, price int), you can find the id of the most expensive items using:

select p1.id
from products p1 left join products p2
on p1.price < p2.price
where p2.id is null;

Fiddle