Find Highest Value in the column in a Table

It is used to find the highest value in the table using different formats.

We can use MAX,Row Number, Dense Rank For this operation.

create table mark
(

id int identity(1,1),

mark int
)

--insert into mark values(98)
select * from mark
--1 st highest

select top 1 mark from mark order by mark  desc
--3 rd highest mark
SELECT
    TOP 1 mark
FROM
    (SELECT DISTINCT TOP 3 mark FROM mark ORDER BY mark DESC) a ORDER BY mark
    
    
    --second highest
SELECT * FROM mark
WHERE mark =
        (SELECT MAX(mark) FROM mark
           WHERE mark <> (SELECT MAX(mark) FROM mark )
        );
        

 select distinct top 3 mark from mark  order by mark desc      
 -- 2 nd highet    
        
        SELECT mark.*
FROM mark
INNER JOIN (
SELECT TOP 1 * 
FROM ( 
   SELECT DISTINCT TOP 2 mark
   FROM mark 
   ORDER BY mark DESC 
) AS innerTable 
ORDER BY mark ASC) joinTable ON mark.mark = joinTable.mark
        
--easy(Using Row Number)
select * from (
select *,ROW_NUMBER()over (  order by mark desc)rn  from mark ) a where rn=3

-- Using Dense Rank

select * from (
select dense_rank() over (order by mark desc) as num, * from mark) a
where num=3




--find 2 nd or 3 rd highest (just change top 2 or 3 )
select top 1 mark from (select distinct Top 2 mark  from mark order by mark desc )a order by mark   

select * from mark 

-- 2 nd highest

Select MAX(mark) From mark Where mark<>(Select MAX(mark) From mark)