CODEWITHSUNDEEP
sqloracle-databasemax
Sia
Sia

Reputation: 1

Max value of a column based on another max value.SQL

I want the max value of the load number and the max value of the load sequence number based on the max load number.

So let's say I have this:

id  | load_no |  load_seq_no |
----|---------|--------------|
1   |  200    |     1        |
----|---------|--------------|
2   |  200    |     2        |
----|---------|--------------|
3   |  200    |     3        |
----|---------|--------------|
4   |  300    |     1        |
----|---------|--------------|
5   |  300    |     2        |
----|---------|--------------|
6   |  300    |     2        |
----|---------|--------------|

and I want to select this

id  | load_no |  load_seq_no |
----|---------|--------------|
5   |  300    |     2        |
----|---------|--------------|
6   |  300    |     2        |
----|---------|--------------|

I want to get those results after full join a simplified version of my code is

{SELECT coalesce(table1.id, table2.id) AS id ,max(table1.load_no) OVER (PARTITION BY table1.id) ,table1.load_seq_no ,table2.load_seq_no FROM table1 FULL JOIN table2 ON (table1.id = table2.id) WHERE table1.load_no = (SELECT max(table1.load_no) FROM table1) OR table2.load_no = (SELECT max(table2.load_no) FROM table2) AND table1.load_seq_no = (SELECT max(table1.load_seq_no) FROM table1) OR table2.load_seq_no = (SELECT max(table2.load_seq_no))}.

AND I ONLY get the max load_no NOT the max load_seq_no.

Upvotes: 0

Views: 81

Answers (2)

user10679624
user10679624

Reputation:

Could this fit your needs?

with x as(
select  max(load_seq_no) as mlsn, max(load_no) as mln  from tableName 
where load_no = (select max(load_no) from tableName )
) select * from tableName 
 join x on load_seq_no = mlsn and load_no = mln

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

you can try like below

  select * from t
  where load_no = (select max(load_no) from t)
  and load_seq_no = (select max(load_seq_no) from t
                            where load_no =(select max(load_no) from t)
                      )

Upvotes: 2

Related Questions