How to fetch maximum record from my table in mySql database

Hi. I've a table purchases which has the following fields:

Code:

---

purchase_id
vendor_id
fuel_type_id
purchase_quantity_liters
purchase_price_per_liter
date_of_purchase
is_deleted

---

I want to fetch that latest record which has fuel_type_id = 1.

Actually, this table gets entry when the fuel (petrol/ diesel) is purchased. So I want to fetch only that record of purchase which has fuel_type_id 1 or 2.

I've used this query but its not showing the latest record:

Code:

---

SELECT
    MAX(p.purchase_id), Id
    p.purchase_price_per_liter,
    p.fuel_type_id
FROM
    purchases p
WHERE
    p.fuel_type_id = 1;

---

There are currently only 3 records, 2 records for fuel_type_id 1 and 1 for ID = 2;

The result this query brings me has the latest ID but not the latest Price?

Please guide me that what I'm missing?

Aggregate functions like Max() only apply to the field they are on, they do not affect the other fields.

In order to get just one record you could use various options, including:

Code:

---

SELECT
    p.purchase_id Id,
    p.purchase_price_per_liter,
    p.fuel_type_id
FROM
    purchases p
WHERE
    p.fuel_type_id = 1;
    AND p.purchase_id = (
        SELECT MAX(purchase_id)
        FROM purchases
        WHERE p.fuel_type_id = 1
        )
;

---

For MySQL8

Code:

---

WITH
    CT    AS (SELECT *,
            ROW_NUMBER() OVER(PARTITION BY fuel_type_id ORDER BY date_of_purchase DESC) AS RN
            FROM purchases)
SELECT * FROM CT WHERE RN=1

---

For MySQL5.7 and older

Code:

---

SELECT purchase_id, vendor_id, fuel_type_id, purchase_quantity_liters, purchase_price_per_liter, date_of_purchase, is_deleted FROM (
  SELECT *, IF(@prev <> fuel_type_id, @rn:=0,@rn), @prev:=fuel_type_id, @rn:=@rn+1 AS rn
  FROM purchases, (SELECT @rn:=0) rn, (SELECT @prev:='') prev
  ORDER BY fuel_type_id ASC, date_of_purchase DESC
) t WHERE rn = 1;

---

Code:

---

SELECT * FROM purchases WHERE purchase_id =
(SELECT TOP 1 purchase_id FROM purchases WHERE fuel_type_id = 1 ORDER BY date_of_purchase DESC)

---

Quote:

---

Originally Posted by digitalShaman

Code:

---

SELECT * FROM purchases WHERE purchase_id =
(SELECT TOP 1 purchase_id FROM purchases WHERE fuel_type_id = 1 ORDER BY date_of_purchase DESC)

---

---

That’s for MS SQL
OP states MySQL