How can I find the second maximum number in an sql table

Question

please suggest

Answer 1

SELECT MAX( column ) FROM table WHERE column < ( SELECT MAX(column) FROM table )

Answer 2

for that I think you have write a procedure in if else loop but if you want to go by a query I suggest you to have some relating attributes or a primary key so that you can manipulate to get the output.

Answer 3

Use the following syntax:
SELECT <column_list>
FROM table_name
ORDER BY column_name DESC
LIMIT BY 2;

Hence, the second row from the result-set of the above query will show the second maximum number from the required column.

Answer 4

it gives error saying that max function cannot be used in above function

Answer 5

with result as
(
select dense_rank() over(order by salary desc) as 'SecondMaximum',* from tablename
)

select * from result where SecondMaximum=2;

Answer 6

IF OBJECT_ID (N'Employee' , N'U' ) IS NOT NULL
DROP TABLE Employee;

CREATE TABLE Employee
(
EmployeeId INT PRIMARY KEY ,
Salary Numeric( 18,2 )
);

Insert into Employee Values ( 101,20000.00 );
Insert into Employee Values ( 102,25000.00 );
Insert into Employee Values ( 103,30000.00 );
Insert into Employee Values ( 104,35000.00 );
Insert into Employee Values ( 105,35000.00 );
Insert into Employee Values ( 106,45000.00 );

SELECT * FROM Employee ;

SELECT EmployeeId, Salary
FROM
(
Select EmployeeId, Salary, ROW_NUMBER() OVER(Order by Salary Desc) as Salary_Order
from Employee
) DT
WHERE DT. Salary_Order = 1 ;