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).
;WITH cte AS
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Material ORDER BY ID DESC) AS rn
FROM table
)
SELECT material,quantity
FROM cte
WHERE rn = 1
In this way you can see the result for "X", "Y" and so on.
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
Your sub query is not correlated to your main query and is returning max id over whole table. Change to
SELECT quantity
FROM table t
WHERE name = 'X' AND
ID = (SELECT max(ID) FROM table t1 where t1.name = t.name)
