Warm tip: This article is reproduced from stackoverflow.com, please click
join left-join oracle select sql

Oracle. get max value from the select ad then compare it

发布于 2020-04-03 23:44:15

So i'm having some troubles with select and idea how to make it work.

       SELECT data1.discount, data7.osp_id, data6.id AS sero_id, data1.estpt_id, data5.festpae_id, MAX(data5.festpae_id) as max_id FROM database1 data1
    JOIN database2 data2 ON data2.id = data1.attr_id
    JOIN database3 data3 ON data3.attr_id = data2.id
    JOIN database4 data4 ON data4.objt_attr_id = data3.id
    JOIN database5 data5 ON data5.stya_id = data4.id
        AND data5.value = 1
    JOIN database6 data6 ON data6.id = data5.sero_id
    JOIN database7 data7 ON data7.id = data6.data7_id
    JOIN database8 data8 ON data8.code = 'CALC1'
WHERE data1.interest_rate = 1 AND data1.ordet_id = data8.id
    AND data1.objt_attr_id = data4.objt_attr_id 

This is my select witch is not working properly. I'm getting an error like

ORA-00937: not a single-group group function

As i understand that happens because of the MAX() parameter.

The output without MAX() is this one:

discount osp_id sero_id estpt_id festpae_id
40       619356 3931831 2144     2000742
50       619356 3931831 2144     2000743
40       619356 3931831 2144     2000737
50       619356 3931831 2144     2000740

So as you can understand i need to get MAX value of festpae_id => 2000743

The idea at the end is to get MAX value of festpae_id and compare it with data5.festpae_id, that is needed to get 1 actual record, not all of them.

So the final output should be this:

discount osp_id sero_id estpt_id festpae_id
50       619356 3931831 2144     2000743

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?

Questioner
Viktor
Viewed
49
Littlefoot 2020-01-31 21:38

Generally speaking, columns not being aggregated should be part of the GROUP BY clause. There are quite a few tables you joined and I didn't understand what goes where, but - see if this helps (note comments I wrote):

  SELECT MAX (data1.discount),                        --> apply aggregate here
         data7.osp_id,
         data6.id AS sero_id,
         data1.estpt_id,
         -- data5.festpae_id,                         --> remove this
         MAX (data5.festpae_id) AS max_id
    FROM database1 data1
         JOIN database2 data2 ON data2.id = data1.attr_id
         JOIN database3 data3 ON data3.attr_id = data2.id
         JOIN database4 data4 ON data4.objt_attr_id = data3.id
         JOIN database5 data5
            ON     data5.stya_id = data4.id
               AND data5.VALUE = 1
         JOIN database6 data6 ON data6.id = data5.sero_id
         JOIN database7 data7 ON data7.id = data6.data7_id
         JOIN database8 data8 ON data8.code = 'CALC1'
   WHERE     data1.interest_rate = 1
         AND data1.ordet_id = data8.id
         AND data1.objt_attr_id = data4.objt_attr_id
GROUP BY data7.osp_id, data6.id, data1.estpt_id       --> add all non-aggregated columns here

[EDIT]

With sample data you posted & query I suggested applied to it, the result seems to be OK:

SQL> with test (discount, osp_id, sero_id, estpt_id, festpae_id) as
  2  (
  3  select 40,       619356, 3931831, 2144,     2000742 from dual union all
  4  select 50,       619356, 3931831, 2144,     2000743 from dual union all
  5  select 40,       619356, 3931831, 2144,     2000737 from dual union all
  6  select 50,       619356, 3931831, 2144,     2000740 from dual
  7  )
  8  select max(discount) discount,
  9         osp_id,
 10         sero_id,
 11         estpt_id,
 12         max(festpae_id) festpae_id
 13  from test
 14  group by osp_id,
 15           sero_id,
 16           estpt_id;

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

SQL>

As you can see, only one row is being returned with FESTPAE_ID = 2000743 (the last one, as you wanted). If you got a different result, there must be something else involved. What would that be, I can't tell - apparently, query works OK (from my point of view).