sql-server


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.

Related Links

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
Informatica powercenter
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

Categories

HOME
json
reverse-engineering
sed
freepascal
system-verilog
bing
steam
mapbox-gl-js
rocketmq
javamail
flyway
proguard
internet-explorer-11
flexbox
onsen-ui2
xlsx
nuxt.js
squarespace
yeoman-generator
off-canvas-menu
tree-traversal
zoho
propel2
ejbca
kadanes-algorithm
ng2-dragula
plsqldeveloper
tflearn
hanami
jenkins-job-dsl
continuous-deployment
cep
pentaho-report-designer
hana-studio
vertex-buffer
jboss5.x
lmdb
piecewise
clockwork
outsystems
spring-insight
pdflatex
spreedly
boilerplate
fax
tic-tac-toe
productivity
microdata
ttcn
seamless-immutable
scd
fedora20
pagefile
in-memory-database
payu
system.web.ui.webcontrols
gitweb
zurb-foundation-apps
com-interop
search-regex
post-processing
axes
spservices
plone-3.x
java.util.calendar
coldfusion-7
data-import
hyperthreading
enyo
js-cookie
vine
divide-by-zero
criteria-api
google-hadoop
client-side-validation
named-ranges
ember-addon
dache
proxies
magic-numbers
dvcs
jstack
factors
anjuta
gfs
facebook-chat
installshield-2009
execvp
jqmobi
stagefright
awesomeprint
bulbs
n-layer
static-variables
windows-phone-7-emulator
hibernate3
ajax.beginform
printing-web-page
paintcomponent
clause
unreachable-code
graniteds
opcodes

Resources

Mobile Apps Dev
Database Users
javascript
java
csharp
php
android
MS Developer
developer works
python
ios
c
html
jquery
RDBMS discuss
Cloud Virtualization
Database Dev&Adm
javascript
java
csharp
php
python
android
jquery
ruby
ios
html
Mobile App
Mobile App
Mobile App