This is the data I have in my table. What I want is maximum sequence number for each order number.
Order No seq Sta
--------------------
32100 1 rd
32100 3 rd
23600 1 rd
23600 6 rd
I want to get the following result without using cursor.
Output:
Order No seq Sta
-----------------
32100 3 rd
23600 6 rd
If you want entire records you could use ROW_NUMBER:
SELECT *
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY Order ORDER BY No_Seq DESC) AS rn
FROM tab) s
WHERE rn = 1;
Please do not use keywords like Order and spaces in column names.
The most simple solution is using group by with max.
Give this a try:
select [Order No], max(seq), Sta
from myTable
group by [Order No]
Sta has no agg function and it isn't in group by.
Just use group by order no and order by sequence desc and you will get your record.
If you are using Oracle Database then you can use ROW_NUMBER() analytical function to achieve this result
Try the below query:
select
*
from
(select
ROW_NUMBER() OVER (PARTITION BY order_no ORDER BY seq DESC) as "ROW_NUM",
order_no, seq, sta
from
Order_Details) temp
where
temp.row_num = 1 ;
The following is probably the most efficient solution in most databases (with the right index):
select t.*
from t
where t.seq = (select max(t2.seq) from t t2 where t2.orderno = t.orderno);
You can also do this with group by:
select orderno, max(seq), sta
from t
group by orderno, sta;
Note that all columns referenced in the select are either group by keys or arguments to aggregation functions. This is proper SQL.
Staunique perOrder No?