how to find 2nd highest salary from table

There is the brute force method:

select max(salary) from my_table where salary < (select max(salary) from my_table);

Or, this should work as well:

select salary from
(select salary, row_number() over (order by salary desc) rn from my_table)
where rn = 2;

you can use the nth_value analytic

nth_value(sal,2) over(order by sal desc)

select max(salary) from my_table where salary < (select max(salary) from my_table);


is it reurn second highest row from the table

SELECT DISTINCT (a.sal) FROM myTable A WHERE 2 = (SELECT COUNT (DISTINCT (b.sal)) FROM myTable B WHERE a.sal<=b.sal);

it should. try it,

if it doesn't produce the results you expect, please post sample data that illustrates the problem

It definitely should do what you want.  The only case that it would not handle is if there are 2 rows with the same value that are the highest.  Then what do you do?

If the 3 highest salaries are:

100
100
99

Which do you want? 100 or 99?  Use the analytic functions if you want 100.  If you want 99, then use the one with max.

you could replace row_number with dense_rank to get 99 also

if i want 99 please give me a query for that

SELECT DISTINCT (a.sal) FROM myTable A WHERE 3 = (SELECT COUNT (DISTINCT (b.sal)) FROM myTable B WHERE a.sal<=b.sal);