HOW TO FIND Nth HIGHEST NUMBER THE COMMON INTERVIEW QUESTION. [OFFSET/FETCH USE TO FIND THE SAME]

>>>>>>HOW TO FIND Nth HIGHEST NUMBER<<<<<

--create a table

create table #temp1

(

id int

)

--insert all the records

insert into #temp1(id)

select 100

union all select 200

union all select 300

union all select 400

union all select 500

union all select 600

select * from #temp1

==Now lets see how to find any row number===================

select top 1 * from #temp1 where id not in

(select top 1 * from #temp1)

--inner query gives 100. select top from rest (200,300,400,500). so 200--Now this gives me the 2nd row pretty cool...

--NOw let me try to get the 3rd row of the table... select top 1 * from #temp1 with(nolock) where id not in(select top 2 * from #temp1)

--o/p 300 superb

--so we got a technique to find out any row number from a table which we want.

--But wait our goal was to find second highest number in the table

 <<<<USE MAX TO GET 2nd highest number>>>

select max(id)  from #temp1 with(nolock) where id not in

(select max(id)  from #temp1)

--the inner query gives me 600. now get the maximum from the numbers leaving 600.So 500.

--Wow that is cool ..so we have replaced top with max

=======================================================================

We have found a simple answer... Now here you may be trapped by an interviewer ...If you are asked to find the 3rd highest... this is not GOING TO  work

===trick 1 to get third highest==========================

--Now lets try this 3rd highest is 400. we need to get 400 by hook or crook.

select min(id) from #temp1 where id in(

select top 3  id from #temp1 order by id desc)

inner query gives me

--so out of that which is minimum. Obviously 400. cooool :)

--Now we are using Min and finding that 400. 

Lets make some twist.we will not use Min.we will use only top.Have a look at the below query incredible :)

===============trick 2 to get third highest=============================

select top 1 * from #temp1  where id in(

select top 3  id from #temp1 order by id desc)

order by id asc

--explanation...

--inner query gives the above result.

--Then we are arranging by ascending order[400,500,600] and fetching the top value.,,,,quite simple....

==============trick 3 use of Row number And Rank number================

select * from

(select id, ROW_NUMBER() over (order by id desc) as rownum from #temp1

)r

where rownum=3

--the inner query gives us the below result. Then rownum=3 is 400.

--o/p 4 400 this is really awesome.

--================correlated queries============================

select * from #temp1 main where

3=(select count(*) from #temp1 auxillary where auxillary.id>=main.id)

--explanation

--Main        Auxillary

--100         --100

--200         --200

--300         --300

--400         --400

--500         --500

--600         --600

--now pick 100 from main table ... now check in auxillary table how many in auxillary table are greater than equal to 100 in main table 

---answer is 6

--now pick 200 from main table ... now check in auxillary table how many in auxillary table are greater than equal to 200 in main table

 ---answer is 5

--now pick 300 from main table ... now check in auxillary table how many in auxillary table are greater than equal to 300 in main table 

---answer is 4

--now pick 400 from main table ... now check in auxillary table how many in auxillary table are greater than equal to 400 in main table

--- answer is 3====we need this one

--now pick 500 from main table ... now check in auxillary table how many in auxillary table are greater than equal to 500 in main table 

---answer is 2

--now pick 600 from main table ... now check in auxillary table how many in auxillary table are greater than 100 in main table 

---answer is 1

-- This is pretty cool. Hope you understood that

--======Same thing but using self join=================================

select main.id from #temp1 Main

join #temp1 auxillary on main.id=auxillary.id

where 3=(select count(*) from #temp1 auxillary where auxillary.id>=main.id)

=======DISTINCT COMES INTO ROLE NOW==================================

--100

--100

--200

--300

--400

--400

--500

--600

--600

-- In these cases use distinct

--lets try this

insert into #temp1(id)

select 400

union all select 600

select * from #temp1

--100

--200

--300

--400

--500

--600

--400

--600

--now let me try the correlated query

select * from #temp1 main where

3=(select count(*) from #temp1 auxillary where auxillary.id>=main.id)

select main.id from #temp1 Main

join #temp1 auxillary on main.id=auxillary.id

where 3=(select count(*) from #temp1 auxillary where auxillary.id>=main.id)

--explanation--> picking the value from main table ...and checking in auxillary table that is   how many are greater than main table[3=3]

i.e if 500 is picked we see that ..in auxillary table.[500,600 and 600]. so answer is 500.

--oops 500 which is not the 3rd highest

--this is where your distinct comes into role

select distinct(id) from #temp1 main where

3=(select count(distinct(auxillary.id)) from #temp1 auxillary where auxillary.id>=main.id)

select distinct(main.id) from #temp1 Main

join #temp1 auxillary on main.id=auxillary.id

where 3=(select count(distinct(auxillary.id)) from #temp1 auxillary where auxillary.id>=main.id)

 =======================================================================

--Earlier we  used one technique you remember

select min(id) from #temp1 where id in(

select top 3  id from #temp1 order by id desc)

--Now as we have inserted duplicated this wont work.Use distinct with top like below >> remember top should be followed by distinct,,,many do wrong here like top 3 disitinct(id) which is wrong

select min(id) from #temp1 where id in(

select  distinct top 3 id   from #temp1 order by id desc)

========WONDERFUL CONCEPT NOW[FETCH OFFSET] IN SQL SERVER 2012========

THIRD HIGHEST

SELECT distinct(id)

FROM #temp1

ORDER BY id DESC

OFFSET 2 ROWS FETCH NEXT 1 ROWS ONLY;

--O/P 400

--arrange by descending order. leave the first two.fetch the next.That is 400.

========================================================================

--2ND HIGHEST

SELECT distinct(id)

FROM #temp1

ORDER BY id DESC

OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY

This is pretty cool right

--offset 1 means skipping the 1st row and fetch next 1 rows is nothing but the second row which is 2nd highest

========================================================================

As we have learned so far this much why not continue and learn the offset fetch

SELECT distinct(id)

FROM #temp1

ORDER BY id DESC

OFFSET 2 ROWS

--o/p

--400

--300

--200

--100

--offset can be written without fetch but not the vice versa

--it means OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY --you have to write this way

 =======================================================================

Wait if we do like this here both offset and fetch are there but still error [SO WE CANT WRITE FETCH NEXT 0 ROWS].

SELECT distinct(id)

FROM #temp1

ORDER BY id DESC

OFFSET 0 ROWS FETCH NEXT 0 ROWS ONLY;

--error The number of rows provided for a FETCH clause must be greater then zero.

====[FETCH FIRST] OR [FETCH NEXT] BOTH ARE SAME ..BOTH ARE INTERCHANGEABLE=====

SELECT distinct(id)

FROM #temp1

ORDER BY id DESC

OFFSET 1 ROWS FETCH FIRST 5 ROWS ONLY

--O/P

--500

--400

--300

--200

--100

SELECT distinct(id)

FROM #temp1

ORDER BY id DESC

OFFSET 1 ROWS FETCH NEXT 5 ROWS ONLY

--O/P

--500

--400

--300

--200

--100

=============ROW AND ROWS BOTH ARE SAME ..BOTH ARE INTERCHANGEABLE=====

SELECT distinct(id)

FROM #temp1

ORDER BY id DESC

OFFSET 1 ROWS FETCH FIRST 5 ROWS ONLY

--O/P

--500

--400

--300

--200

--100

SELECT distinct(id)

FROM #temp1

ORDER BY id DESC

OFFSET 1 ROW FETCH NEXT 5 ROW ONLY

--O/P

--500

--400

--300

--200

--100

========================================================================

SELECT distinct(id)

FROM #temp1

ORDER BY id DESC

OFFSET 0 ROWS FETCH NEXT 0 ROWS ONLY;

--error The number of rows provided for a FETCH clause must be greater then zero.

 --HOW TO AVOID THIS ERROR

1ST WAY

DECLARE @VAR INT

SET @VAR=0

SELECT distinct(id)

FROM #temp1

ORDER BY id DESC

OFFSET 0 ROWS FETCH NEXT @VAR ROWS ONLY;

--SELECT ALL AND PRESS F5 IT WORKS ,,,, COOL :)

2ND WAY

SELECT distinct(id)

FROM #temp1

ORDER BY id DESC

OFFSET 0 ROWS FETCH NEXT (SELECT 0) ROWS ONLY;

--THIS WORKS AGAIN,,,,SUPERB :)

--NOW THE OFFSET/FETCH IS SIMILAR TO TOP FUNCTION .... THEN WHAT IS THE DIFFERENCE ,,WHAT IS THE ADVANTAGE,,,,WHEN WE SHOULD GO FOR TOP ,,, WHEN WE SHOULD GO FOR OFFSET/FETCH ??

--OFFSET/FETCH ALLOWS SKIPPING WHERE AS TOP DOESNOT.

--ORDER BY is mandatory to use OFFSET and FETCH clause WHICH IS DISADVANTAGE OF OFFSET... WHERE AS ITS NOT MANADATORY FOR TOP

--The TOP filter is more flexible than OFFSET/FETCH in the sense that TOP is allowed in modification statements, whereas OFFSET/FETCH isn’t allowed

--FETCH doesn’t support WITH TIES or PERCENT You can approximate the percentage by using a subquery but you may not get the same number of records as using TOP n PERCENT