Find Nth Maximum value

"How do I find Nth maximum value?" is one of the most asked questions

Here are some methods 

I explain how to find 5th Maximum value

01.declare  @number table(num integer) 

02. 

03.Insert into @number values(3456)

04.Insert into @number values(846)

05.Insert into @number values(1456)

06.Insert into @number values(3098)

07.Insert into @number values(34)

08.Insert into @number values(67856)

09.Insert into @number values(906)

10.Insert into @number values(34656)

11.Insert into @number values(9056)

12.Insert into @number values(3036)

(1) Use Inner Join

1.select t1.num from @number t1 inner join @number t2 on t1.num<=t2.num

2.group by t1.num having count(t1.num)=5

(2) Use Top Operator

1.Select top 1 num from

2.(

3.Select top 5 num from @number order by num desc

4.) T

5.order by num asc

(3) Generate Serial No based on descending order of the

1.select num from

2.(

3.Select (select count(*) from @number where num>=T.num)as Sno ,num

4.from @number as T

5.) as temp

6.where Sno=5

(4) Generate Serial No based on descending order of the values in where Clause

1.select num from @number as n

2.where (select count(*) from @number where num>=n.num)=5

(5) Use Aggregate

1.Select min(num) from (select top 5 num from @number order by num desc) T

(6) Use Row_number() function

1.select num from

2.(

3.select num, row_number() over (order by num desc) as sno from @number

4.) as t

5.where sno=5

6. 

7.Now you can replace 5 to the number that you want to find the maximum value

General SQL query should be as follows

select sal from emp a where N = (select count(distinct sal) from emp b where a.sal <= b.sal) where N = any value.

Using Profiler to get list of SPs on some specific pages in application

1. Run Profiler
2. Choose TSQ_SPs Template
3. On the Event Selection tab select "Show all columns" checkbox
4. On the same page check only one event: SP:Completed
5. On Colum Filters set filters for ApplicationName, DatabaseName, HostName, LoginName. Actually it's enough only DatabaseName and HostName. HostName as my computer name, because I run application locally from VS.
6. Save Trace properties as Template.
7. Run trace
8. Run application.
9. Before loading required page clean trace
10. Open page and pressing different buttons and controls on the page
11. After you finished with all actions on the page, save Trace as Trace Table to db.
12. Running the following query:
    SELECT DISTINCT ObjectName FROM SPTableName
    and getting list of SPs for this page. DISTINCT allows you to eliminate repeating SPs, because on each page SPs run many times.