SQL Question from a beginner

I know that the following will return the highest price in the 'price' column:

Code:

---

SELECT MAX(price)
FROM items_ordered;

---

How can I also get it to show the corresponding item with the max price?

I have played around with it but can't figure it out.

Assuming that your items table is called "items" and that the item name field is called "name"

Code SQL:

---

SELECT
      MAX(orders.price) AS highest_price
    , items.name AS item
FROM
    items_ordered AS orders
INNER JOIN
    items
ON
    items.item_id = orders.item_id

---

Quote:

---

Originally Posted by Andwise

How can I also get it to show the corresponding item with the max price?

---

here's a quick way:

Code:

---

SELECT item, price
FROM items_ordered
ORDER BY price DESC LIMIT 1

---

spacephoenix, i think there might have been only one table involved in this question :)

Quote:

---

Originally Posted by r937

here's a quick way:

Code:

---

SELECT item, price
FROM items_ordered
ORDER BY price DESC LIMIT 1

---

spacephoenix, i think there might have been only one table involved in this question :)

---

I did wonder if it was just one table. Andwise, is it one table or is it two tables?

Quote:

---

Originally Posted by SpacePhoenix

I did wonder if it was just one table. Andwise, is it one table or is it two tables?

---

It is one table. Sorry for not specifying. I never considered using the limit function.

Thanks for your help.