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?
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).
You're welcome. Unfortunately, I can't see images. If the result doesn't fit, now you know the principle so - tweak the query a little bit more.
Yeah, thank you. You've helpful a lot. Now he is returning only 2 max records for 40 and 50 discount. Is there any way to limit it just with one, so he would look on latest one? In this case it's 50, not 40.
I posted some more info within the answer; have a look, please.
Yeah, thank you! Missed something first time!
Last question,can i do this without relying on
discount
value? Because also situations, when like discount with value40
has higherFESTPAE_ID
=> (2000745)