Find Highest Value in the column in a Table

Arul44ece
Posted by Arul44ece under Sql Server category on | Points: 40 | Views : 1070
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)

Comments or Responses

Login to post response