Oracle. Get only 1 record by using max() in select

Question

I'm having select script inside left outer join witch I'm using for getting values

SELECT discount, osp_id, sero_id, estpt_id, max(festpae_id) festpae_id 
  FROM database1 data1
  JOIN database2 data2 ON data2.id = data1.attr_id
 WHERE data1.interest_rate = 1 
   AND data1.ordet_id = data2.id
   AND data1.objt_attr_id = data2.objt_attr_id 
 GROUP BY osp_id, sero_id, estpt_id;

From this select i'm getting this output

DISCOUNT     OSP_ID    SERO_ID   ESTPT_ID FESTPAE_ID
---------- ---------- ---------- ---------- ----------
      50     619356    3931831       2144    2000743
      40     619356    3931831       2144    2000744

This is not correct for me, because i need to get only 1 output, which means I'm looking for highest FESTPAE_ID, so the correct output of the script should be this

  DISCOUNT     OSP_ID    SERO_ID   ESTPT_ID FESTPAE_ID
---------- ---------- ---------- ---------- ----------
        40     619356    3931831       2144    2000744

I know, that if I will use MAX() on dicount row, then he will return me only 1 record with FESTPAE_ID = 2000743, which is not my goal.

What do I need to adjust to make it work as i want? So he would return only 1 record based on higest FESTPAE_ID and do not care other values like discount.

P.S This script is used in view table as LEFT OUTER JOIN, so if i order it by festpae_id and then set rownum = 1 it doesn't work properly in view table(but it works if run separately from whole view table select(as left outer join)), that's why i'm asking your help how to do it with MAX(). Or there is any better way?

Barbaros Özhan · Answer 1 · 2020-02-02T13:20:19.667

You can use analytic functions such as ROW_NUMBER() by decending festpae_id and then filter out the row with the value 1 returned from that function :

SELECT *
  FROM
  (
    SELECT discount, osp_id, sero_id, estpt_id, 
           ROW_NUMBER() OVER 
          (PARTITION BY osp_id, sero_id, estpt_id ORDER BY festpae_id DESC) as festpae_id 
      FROM database1 data1
      JOIN database2 data2 ON data2.id = data1.attr_id
     WHERE data1.interest_rate = 1 
       AND data1.ordet_id = data2.id
       AND data1.objt_attr_id = data2.objt_attr_id 
   )
  WHERE festpae_id = 1

in this case, GROUP BY wouldn't be needed, use PARTITION BY osp_id, sero_id, estpt_id instead.

If your DB version is 12c+, then FETCH clause including WITH TIES option might be preferred :

SELECT discount, osp_id, sero_id, estpt_id, 
       ROW_NUMBER() OVER 
          (PARTITION BY osp_id, sero_id, estpt_id ORDER BY festpae_id DESC) as festpae_id 
  FROM database1 data1
  JOIN database2 data2 ON data2.id = data1.attr_id
 WHERE data1.interest_rate = 1 
   AND data1.ordet_id = data2.id
   AND data1.objt_attr_id = data2.objt_attr_id 
 ORDER BY festpae_id
 FETCH FIRST 1 ROW WITH TIES

Hey! Let me check if it works for me! I will get back to you how it goes. — Viktor, Feb 02 '20 at 09:36

score 1 · Answer 2 · answered Feb 02 '20 at 10:14

This seems like a good place to use the keep option of Oracle aggregate functions:

SELECT 
    MAX(discount) KEEP(DENSE_RANK LAST ORDER BY festpae_id) discount,
    osp_id, 
    sero_id,
    estpt_id, 
    MAX(festpae_id) festpae_id 
FROM database1 data1
JOIN database2 data2 ON data2.id = data1.attr_id
WHERE 
   data1.interest_rate = 1 
   AND data1.ordet_id = data2.id
   AND data1.objt_attr_id = data2.objt_attr_id 
GROUP BY 
    osp_id,
    sero_id,
    estpt_id;

Oracle. Get only 1 record by using max() in select

2 Answers2