Error in creating function in sql
CREATE FUNCTION Salary.SecondHighestSalary() Returns int AS BEGIN Declare #salary int; SELECT TOP 1 salary FROM ( SELECT TOP 2 salary FROM Salary ORDER BY salary DESC ) as maxsalary ORDER BY salary ASC Return #salary END Hello everyone, I am trying to create a sql function of getting the second highest salary by using the above syntax but i am getting the error: "Select statements included within a function cannot return data to a client." Can anyone please tell me what is the actual syntax to write the function or just turn my code back with the correct syntax. Thanks in advance.
CREATE FUNCTION Salary.SecondHighestSalary() Returns int AS BEGIN Declare #salary int; SELECT TOP 1 #salary = salary FROM ( SELECT TOP 2 salary FROM Salary ORDER BY salary DESC ) as maxsalary ORDER BY salary ASC Return #salary END
I'd write is using ROW_NUMBER() as follows: CREATE FUNCTION SecondHighestSalary() Returns int AS begin Return ( select salary from ( select salary,ROW_NUMBER() OVER (ORDER BY salary desc) as rn from Salary ) t where rn = 2); END This also allows it to be easily adapted if you need to deal with e.g. ties (switch to RANK or DENSE_RANK as appropriate) which the TOP and ORDER BY approach isn't so amenable to.
Resume a WHILE loop from where it stopped SQL
Update a table using temp table with select statement for multiple rows
PIVOT table in MS SQl server
Does Dapper request a full object from the database prior to Linq operations?
SQL Server to Postgresql - Database migration - Tool issues
SQL Agent Job to run .bat with gsutil rsync
Tool/script to clone SQL Server database with reduced rights (without backup functionality)?
SQL Server function with parameters
Bulk Load Data Conversion Error - Can't Find Answer
when i exec select on linux with odbc, hang on '__select_nocancel'
The system cannot find the file specified on Production server
An Error when trying to connect to SQL Server
Temporary table not created from dynamic query execution
How to process the data in the table, which is frequently inserted
Combine two columns and input the result in a different columns using SQL server