Search code examples
mysqlsqlgreatest-n-per-group

Query in SQL in a Database

I need your help to write a query in SQL for a database. Imagine this case:

Database where there are 3 columns (ID, Material, Quantity)

id material quantity
1 X 30
2 X 15
3 Y 20

I would like to get last quantity entry for Material X from Database. My idea is to do the following:

SELECT quantity 
FROM table 
WHERE name = 'X' 
AND ID = ( SELECT max( ID ) FROM table)

Unfortunately, I am not able to get value 15. It only works if I search last row only (in this case for material Y).

Solution

  • Filter the table for name = 'X', sort the results by ID in descending order and get the top row with LIMIT:

    SELECT quantity 
FROM tablename 
WHERE name = 'X' 
ORDER BY ID DESC LIMIT 1