I need to show data max from specified colum, is this even possible? I am still newbie on this SQL.
I have data like this
Id Order_id Sequence
------------------------
1 A01 1
2 A01 2
3 A02 1
4 A02 2
5 A02 3
6 A03 1
Id is the primary key.
Expected result
Id Order_id Sequence
------------------------
2 A01 2
5 A02 3
6 A03 1
You need to do a sub-query:
SELECT t.id, t.order_id, t.sequence
FROM t
INNER JOIN
(SELECT order_id, MAX(sequence) as mx
FROM t
GROUP BY order_id) sub
ON t.order_id = sub.order_id
WHERE t.sequence = sub.mx
So basically the query called sub finds the max sequence value for each order ID. Then these results are joined back the main table, which I'm calling t, so you can slap on the id.
Use GROUP BY and ORDER BY
SELECT Id,Order_id,MAX(Sequence) AS Sequence FROM table_name GROUP BY Order_id ORDER BY Id
If you don't need the id in the result, you can just do a group by... otherwise you need an inner query as the answer provided by @kbball.
select order_id, max(sequence)
from tablename
Group by order_id;
You can use correlated subquery as below.
SELECT *
FROM table1 t1
WHERE t1.Sequence =
( SELECT max(t2.Sequence)
FROM table1 t2
WHERE t1.Order_id = t2.Order_id );
Result:
Id Order_id Sequence
------------------------
2 A01 2
5 A02 3
6 A03 1
Group Byclause.