CODEWITHSUNDEEP
sql
Gowdham
Gowdham

Reputation: 153

How to to get maximum sequence number in SQL

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

Upvotes: 1

Views: 16768

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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.

Upvotes: 0

SwapnaSubham Das
SwapnaSubham Das

Reputation: 537

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 ;

Demo

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

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;

DBFiddle Demo

Please do not use keywords like Order and spaces in column names.

Upvotes: 3

Pavan Sikarwar
Pavan Sikarwar

Reputation: 798

Just use group by order no and order by sequence desc and you will get your record.

Upvotes: 0

Koby Douek
Koby Douek

Reputation: 16675

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]

Upvotes: 2

Related Questions