+ 4
How can I find the second maximum number in an sql table
please suggest
6 Answers
+ 5
SELECT MAX( column ) FROM table WHERE column < ( SELECT MAX(column) FROM table )
+ 4
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.
+ 2
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.
+ 1
it gives error saying that max function cannot be used in above function
+ 1
with result as
(
select dense_rank() over(order by salary desc) as 'SecondMaximum',* from tablename
)
select * from result where SecondMaximum=2;
0
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 ;