0

How to get the last row (last itemStoreUnitID) from table of each grouped items?

SELECT * FROM itemStoreUnits isu
LEFT JOIN storeUnits su ON isu.itemStoreUnitStoreUnitID=su.storeUnitID  
LEFT JOIN items i ON isu.itemStoreUnitItemID=i.itemID
LEFT JOIN products p on i.productID=p.productID
GROUP by isu.itemStoreUnitItemID desc

Previously presented query retieves only the first row (oldest ID) of each item group and cannot be ordered in descending order with ORDER BY before GROUP BY.

I have tried a subquery, unfortunately no sucess achieved

SELECT * FROM itemStoreUnits isu
LEFT JOIN storeUnits su ON isu.itemStoreUnitStoreUnitID=su.storeUnitID  
LEFT JOIN items i ON isu.itemStoreUnitItemID=i.itemID
LEFT JOIN products p on i.productID=p.productID
where isu.itemStoreUnitID IN
(
SELECT max(itemStoreUnitID) FROM itemStoreUnits
  )
GROUP by i.itemID desc;

Or

SELECT *,MAX(isu.itemStoreUnitID) FROM itemStoreUnits isu
LEFT JOIN storeUnits su ON isu.itemStoreUnitStoreUnitID=su.storeUnitID  
LEFT JOIN items i ON isu.itemStoreUnitItemID=i.itemID
LEFT JOIN products p on i.productID=p.productID
GROUP by i.itemID desc;

*,Max(..) retieves correct data (last ID); however, those data are not part of the wanted row. Data are just assigned to them as an additional column.

Rudi
  • 77
  • 1
  • 10
  • Look into `row_number()` function. – Isolated Mar 08 '23 at 17:50
  • The first duplicate shows different methods of getting "last" row per group and has lots of explanations why certain methods that you also tried do not work. The 2nd duplicate just shows how to apply a technique if the last row is in a different table. – Shadow Mar 08 '23 at 18:19

0 Answers0