2

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?

Improve this question
0

2 Answers 2

Reset to default
1

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;
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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
Improve this answer

1 Comment

Hey! Let me check if it works for me! I will get back to you how it goes.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.