CODEWITHSUNDEEP
sqloracle-databasegreatest-n-per-group
Faizad
Faizad

Reputation: 15

In oracle How can I Find out one/two Columns data which corresponding other columns have maximum value

I'm Using Oracle where,
I have a Table(FE_IMPORT_LC Table) with data from where i give in following few column with data 

TRANSMIT_LC_NO  LIAB_AMT_LCY    REM_LC_AMT_LCY    IMP_AMEND_NO
108615020048    10022000        10022112          00
108615020048    10022000        10022112          01
108615020048    10022000        10022112          02
108615020048    11692000        8351760           03

I want to find out Data of the Red Marked Rows, which IMP_AMEND_NO column value is maximum. That means I want to find out one/two Columns data which corresponding other columns have maximum value.

So, I already create following query:

SELECT  l1.liab_amt_lcy
FROM    fe_import_lc l1
WHERE   l1.transmit_lc_no = '108615020048'
AND     l1.imp_amend_no  = (SELECT MAX(l2.imp_amend_no)
                            FROM fe_import_lc l2
                            WHERE l2.transmit_lc_no = l1.transmit_lc_no)

But I want more effective query for this, If any one know about it please...
Please give answer/reply as early as possible.

Upvotes: 1

Views: 99

Answers (3)

Pravin Satav
Pravin Satav

Reputation: 702

You can try this, I don't have environment currently to test syntax error. However, I think with little modification it should work fine

select * from 
    (
        select   TRANSMIT_LC_NO,  LIAB_AMT_LCY,    REM_LC_AMT_LCY,    IMP_AMEND_NO,
        row_number() over(partition by transmit_lc_no order by imp_amend_no desc) as MAX_ID
        from fe_import_lc
    ) 
    t where t.MAX_ID=1
    and T.TRANSMIT_LC_NO = '108615020048';

Upvotes: 0

Utsav
Utsav

Reputation: 8143

Try something like below, where l1 would be your FE_IMPORT_LC table. Better to create a view with the logic of l2 table given below and then select.

          with l1(TRANSMIT_LC_NO,  LIAB_AMT_LCY,    REM_LC_AMT_LCY,    IMP_AMEND_NO) as(
          select 108615020048,10022000,10022112,00 from dual union
          select 108615020048,10022000,10022112,01 from dual union
          select 108615020048,10022000,10022112,02 from dual union
          select 108615020048,10022000,10022112,03 from dual
          ), l2 as(
          select l1.*,row_number() over (partition by TRANSMIT_LC_NO order by  IMP_AMEND_NO desc) as rno from l1)
          select TRANSMIT_LC_NO, LIAB_AMT_LCY,REM_LC_AMT_LCY,IMP_AMEND_NO from l2
          where rno=1;

If 2 rows have same max(IMP_AMEND_NO ) and if you want both, use below query(instead of row_number, I am using rank here. Rest same.

          with l1(TRANSMIT_LC_NO,  LIAB_AMT_LCY,    REM_LC_AMT_LCY,    IMP_AMEND_NO) as(
          select 108615020048,10022000,10022112,00 from dual union all
          select 108615020048,10022000,10022112,01 from dual union all
          select 108615020048,10022000,10022112,03 from dual union all
          select 108615020048,10022000,10022112,03 from dual
          ), l2 as(
          select l1.*,rank() over (partition by TRANSMIT_LC_NO order by  IMP_AMEND_NO desc) as rno from l1)

          select TRANSMIT_LC_NO, LIAB_AMT_LCY,REM_LC_AMT_LCY,IMP_AMEND_NO from l2
          where rno=1;

Here you dont have to specify TRANSMIT_LC_NO explicitely. If you have many records, then also you can get only row corresponding to max(IMP_AMEND_NO). But if you want to use this is a PL/SQL block, then put the TRANSMIT_LC_NO in the where clause in the select query from FE_IMPORT_LC and proceed like below.

Upvotes: 0

Praveen
Praveen

Reputation: 9365

Try;

select liab_amt_lcy
from (
    SELECT  l1.liab_amt_lcy, imp_amend_no
    FROM    fe_import_lc l1
    WHERE   l1.transmit_lc_no = '108615020048'
    order by imp_amend_no desc
)
where rownum < 2

Upvotes: 0

Related Questions