With CTE in SQL | Common Table Expression | Find nth Max Salary in SQL

With CTE in SQL | Common Table Expression | Find nth Max Salary in SQL
Blog - www.testerhoon.com
Explained -
Syntax
-Usage
-example to find out nth max salary number
#sql
#etltesting

[@kameswari11]

Awesome!! Excellent explanation thank you.

[@ashishkumar-on8xg]

Thankyou for this informative video, explained in very clear and simple way. Thanks

[@testerhoon28]

😊👍

[@VikramSingh-st8jd]

Great work Miss👍

[@testerhoon28]

thank you 🙏

[@Crazy_Experimental_Show]

Keep doing good work

[@HaroonKhan-pz8wb]

Very informative thanks for sharing such a video. Please make video on real time ETL tool.

[@testerhoon28]

sure, thanks for watching 🙏

[@amitprasad5471]

Very informative content..👍
Ma'am, please Azure Data k basic bhi btye..

[@testerhoon28]

okay 👍

[@VikramSingh-st8jd]

Please bring more lecture videos asap

[@testerhoon28]

sure . 👍

[@kartikjuneja7807]

Hello Mam,
What if we have the same salary for 3 persons/ids ?

[@testerhoon28]

then all will fall under the same rank.. 🙏

[@mrrahaman6246]

If there is a duplicate records in salary column then how it will work???

[@testerhoon28]

in that case use dense_rank() function 🙏

[@thelayer5211]

I don't know why but I feel this is wrong bcoz I always used dense_rank() if there r duplicate records that condition if we r used row_number then v get wrong output

[@testerhoon28]

this i have used for my test data with 5 records and all having different salary... u could use dense rank if u have multiple same salary... 🙏

[@krishnay.233]

Here, we have to hardcode the count value of row number to get the nth salary, is there a way to make this query more dynamic?

[@testerhoon28]

you could copy that code and make stored procedure and pass as a variable..

[@ShivShwetaVlogs]

How to use Row Number when we have duplicate or same salary numbers.

[@testerhoon28]

in that case u could use dense rank instead of row number...

[@Manifestion_kannada]

Mam in how many we can be perfect in SQL

[@testerhoon28]

Practice Practice and practice... thats the only way to bcm perfect 🙏✌

[@kishore_tope_9597]

ma'am i think this is a wrong function you used.
for nth max salary we use dense_rank()
beacuse if the salary is duplicate then how row_number() works?
you didn't take duplicate salary thats why the ans is showing right..
but i said this is wrong..
ma'am please correct me if i am wrong...🙏

[@testerhoon28]

in the given example no duplicate data i took hence used it..... if there are duplicate u have to use dense_rank...

[@gauravsingh8274]

You can do it simply but why did you use cte for that

[@testerhoon28]

yes, we can do it.. just to explain the CTE i have used it.. m🙏

[@gauravsingh8274]

i am asking difference between cte for simply query

[@testerhoon28]

@@gauravsingh8274 CTE we use to store a resultset that we can use further in the query... its up to u... u could use it anywhere as per ur need..

[@gauravsingh8274]

@@testerhoon28 ok waiting you videos

[@thelayer5211]

Now a days interviewers ask very complex query what can I do now

[@testerhoon28]

that comes from practice... the more u practice on real scenerios .. the more u get expertize on sql.. 🙏

[@RimaraniMeher]

It's not desc order maam

[@testerhoon28]

you can either use Describe or Desc

[@akshaydudhe9395]

can you please share ur LinkedIn?

[@testerhoon28]

I do not have LinkedIn account... 🙏

[@akshaydudhe9395]

@@testerhoon28 Can you please refer me in IBM for Azure data engineer role?

[@testerhoon28]

@@akshaydudhe9395 pls share ur resume at [e-mail]

[@akshaydudhe9395]

@@testerhoon28 yes sent