Oracle interesting questions and answers | SQL to find the N th maximum salary

Oracle interesting questions and answers | SQL to find the N th maximum salary without using rownum or rank function or subquery

--------------------------------------------------------------
This channel is for learning Oracle SQL, PLSQL, DATABASE concepts, MYSQL, ETL, Mongo DB, Python,UNIX and related technologies.
--------------------------------------------------------------

--------------------------------------------------------------

--------------------------------------------------------------

--------------------------------------------------------------
About Myself:
----------------------
I am Siva, [LEARN | CODE | TRAIN | SHARE].
Being in IT industry for more than 12+ years.
In my day to day job, I work with database technologies including Oracle, Java, Python, MongoDB, talend and UNIX.
I am passionate about "DATA", coding & training.
In my spare time, I teach database technologies , ETL etc.
I am very glad that you are reading my Profile, and I value your time as well as mine.
Looking forward to see you in my videos

--------------------------------------------------------------

[manideepkumar]

1st rownum u explained well, later ur simply typing something and not explaining what r u writing, in subquery why r u writing b.sal>=a.sal?

[saktibiswal]

Subscribed!! Awesome Channel and very good explanation! Definitely learnt from this

[abheshkumar]

Write a procedure to update salary 10% and select employees. Without using parameters.

[bhaskaro]

Thanks for explaining different methods to find the N'th max salary, also please make a video which methods of these are efficient.

[anushaa]

Sir, thank you for this GREAT VIDEO👍. Could you please elaborate the method 3 approach .? Why we are taking count of distinct salary there?

[deepakranjanmishra]

Sir very good explanations.Thank you so much for your extended support to make it.Much appreciated.

[devinenibalaji]

Hi siva,

Request to pls clearly explain the subquery method

[crazycom]

Thanks a lot for such a detailed explanation..

[sravankumar]

Hi .... Any video which will return multiple rows using object type in function?

[minicat]

Or use nth_value function
Select distinct nth_value(sal, 5) over (order by sal desc range between unbounded preceding and unbounded following) rn
From emp;

[feyie]

Hi Siva, can u help me?
I need to get the sum of the total value of each element that's grouped per pay date. And I need to display it in my report as one row .
Here's the sample data (4 rows):

Column1: Pay date
15-Jan-2022
30-Jan-2022
15-Jan-2022
30-Jan-2022
Column2: element
Pay allowance
Pay allowance2
Pay allowance
Pay allowance2

Column3: total
500
300
500
300

I used a case statement but it doesn't work.

SELECT a.personid, TRUNC(TO_DATE(a.pay_date, 'DD-MON-YYYY')) pay_date
CASE WHEN a.element LIKE 'Pay Allowance%' OR 'Pay Allowance2' THEN 'Pay Allowance Benefit'
ELSE NULL
END as Pay_Allowance_Benefit
, SUM(a.total)
FROM
(SELECT tbl4.personid, TRUNC(TO_DATE(tbl3.val_date, 'DD-MON-YYYY')) pay_date
, tbl2.name element
, tbl1.value total
FROM tbl1
, tbl2
, tbl3
, Tbl4
Where clause) a
WHERE a.personid = :p_id
GROUP BY a.element
, a.person_id
, TRUNC(TO_DATE(a.pay_date, 'DD-MON-YYYY'))

[sampurnabhattacharjee]

will this query work for the above question
Select Salary
FROM(SELECT DISTINCT SALARY
FROM EMPLOYEE
ORDER BY Salary DESC
FETCH FIRST 2 ROWS ONLY)
ORDER BY SALARY ASC
FETCH FIRST 2 ROWS ONLY

[PrayasDhanbadBasicBuilder]

Seems fetch first don't work in 11g. So kindly do mention the oracle version. If possible in each video.

[SatyendraSingh-uliq]

Thanks shiva, please let us know that difference between force view and inline view.

[prabhus]

Great content. I don't know who is the one disliked this video

[dfhwze]

Can you also do it using match_recognize or a recursive function?

[ashokkumar-hmon]

select salary rank() over(order by salary desc), DENSE_RANK()OVER(order by salary desc)from employees


not working

[SriLife]

Can u do the same to find group wide nth sal without rank, analytical, rownum functions.

[mukeshchitte]

Great, happy to listen to u, thanks a lot 🙂

[sumanthreddy]

sir can i have latest recorded videos if i pay u the money..