Get last order entry for a particular user from the table

[cyberdoc]

Hi guys,


ive a table with entries of users and the orders they made and payments details etc. now i need a query wherein only the latest orders for all users are shown and not all of them. for example,


ORDERID | USERID | ORDERDATE | AMOUNT | PAID | BALANCE |
ORD0001 | USR0001| 12/2/17 | 1500 | 1200 | 300 |
ORD0002 | USR0002| 12/2/17 | 400 | 210 | 190 |
ORD0003 | USR0002| 12/2/17 | 1000 | 800 | 200 |
ORD0004 | USR0003| 12/2/17 | 500 | 200 | 300 |


SO in this we would want
only order 3 to be shown for user2 and not order2.
Thanks in advance

[CJ_London]

to get the last order, you need one or more fields to indicate a sort order and therefore can identify the last record.

So assuming orderids increment on creation then you could use

SELECT T.*
FROM tblOrders T INNER JOIN (SELECT UserID, max(OrderID) maxOrder FROM tblOrders GROUP BY UserID) Q ON T.UserID=Q.UserID AND T.OrderID=Q.maxOrder

If they don't increment then you would also need to use the Orderdate field as well, but in your example all orders have the same date, so no discernible order can be made.

[cyberdoc]

to get the last order, you need one or more fields to indicate a sort order and therefore can identify the last record.

So assuming orderids increment on creation then you could use

SELECT T.*
FROM tblOrders T INNER JOIN (SELECT UserID, max(OrderID) maxOrder FROM tblOrders GROUP BY UserID) Q ON T.UserID=Q.UserID AND T.OrderID=Q.maxOrder

If they don't increment then you would also need to use the Orderdate field as well, but in your example all orders have the same date, so no discernible order can be made.

orderid do increment but the query gives a syntax error and doesnt work

[CJ_London]

since I guessed at your names, I'm not surprised if you have just copy and pasted. Wild guess is you've spelt something incorrectly or not used proper syntax.