Query

I have a table
Emp

EmpID, Emp_name, Salary
1 a 100
2 b 200
3 a 1200
4 a 1000


I want to write function has return EmpID the emp who had third max salary

Any help thank in adv.

select*
fromEmp
whereSalary= (select min(Salary) from (select top 3 Salary from Emp order by Salary desc)s)



KH

>> write function
oh missed this



create function dbo.yourfunc
returns int
as
begin
declare @empid int

select@empid = EmpID
fromEmp
whereSalary= (select min(Salary) from (select top 3 Salary from Emp order by Salary desc)s)

return @empid
end



KH

thank so much

could you not use

select top 3 @empid = empid from emp order by salary desc

in a function

to give the the third highest paid employee.

Cheers
Twan

PS NOTE that these both assume that salary is unique, if this is not the case then you'd need to use khtan's solution but adding a distinct to the select top 3 salary clause, and you'd have to decide which empid to get if the third highest salary was paid to multiple people too

Hi,

This could be another solution got some more scope for improvement but with a different way of writing the same code.

select e.*
from humanresources.employee e join (
select row_number() over (order by vacationcount desc) as rank,
emp.vacationhours, emp.vacationcount
from (select vacationhours, count(*) as vacationcount
from humanresources.employee
group by vacationhours) as emp

) g
on e.vacationhours = g.vacationhours and g.rank = 3



Kazi Hassan

Twan,

quote:Originally posted by Twan

could you not use

select top 3 @empid = empid from emp order by salary desc

in a function

to give the the third highest paid employee.

Cheers
Twan

PS NOTE that these both assume that salary is unique, if this is not the case then you'd need to use khtan's solution but adding a distinct to the select top 3 salary clause, and you'd have to decide which empid to get if the third highest salary was paid to multiple people too

AS we discussed before, this behaviour is not guaranteed. ORDER BY just tell how to order the resultset, Not the internal execution order.

Now try adding a DISTINCt to the query and you can see how it behaves.

Roji. P. Thomas
http://toponewithties.blogspot.com

Here is my version

create function dbo.yourfunc(@n int)
returns int
as
begin
declare @empid int

select@empid = EmpID
fromEmp E
where@n = (select Count(*) from Emp I WHERE I.salary <= E.Salary)

return @empid
end

Roji. P. Thomas
http://toponewithties.blogspot.com

quote:Originally posted by FrankKalis

quote:Originally posted by Roji. P. Thomas

Twan,

quote:Originally posted by Twan

could you not use

select top 3 @empid = empid from emp order by salary desc

in a function

to give the the third highest paid employee.

Cheers
Twan

PS NOTE that these both assume that salary is unique, if this is not the case then you'd need to use khtan's solution but adding a distinct to the select top 3 salary clause, and you'd have to decide which empid to get if the third highest salary was paid to multiple people too

AS we discussed before, this behaviour is not guaranteed. ORDER BY just tell how to order the resultset, Not the internal execution order.

Now try adding a DISTINCt to the query and you can see how it behaves.

Roji. P. Thomas
http://toponewithties.blogspot.com

But in both cases, the value is assigned to the variable after the sort has taken place and that's what is wanted here. Or am I missing something?

Frank,

The value is NOT actually assigned AFTER the sort. Execute the following script. You can see that intermediate results are assigned to the variable.


CREATE TABLE #Test(someval int)
INSERT INTO #Test VALUES(1)
INSERT INTO #Test VALUES(2)
INSERT INTO #Test VALUES(3)
INSERT INTO #Test VALUES(4)

DECLARE @i int
SET @i = 1
SELECT @i = @i * someval
FROM #Test
ORDER BY someval

SELECT @i

Roji. P. Thomas
http://toponewithties.blogspot.com

Good Reading on the topic.

http://support.microsoft.com/default.aspx/kb/287515

http://www.projectdmx.com/tsql/rowconcatenate.aspx


Roji. P. Thomas
http://toponewithties.blogspot.com

For a simple assignment to a variable this makes no difference though... the variable will have the last value in the result set as sorted by the order by. always has and surely always will... it would make no sense otherwise...?

Cheers
Twan

quote:Originally posted by Twan

For a simple assignment to a variable this makes no difference though... the variable will have the last value in the result set as sorted by the order by. always has and surely always will... it would make no sense otherwise...?

My point is that this behaviour is not documented and so not guaranteed. Period.

Roji. P. Thomas
http://toponewithties.blogspot.com

I think Roji might be confusing UPDATE queries, which are not guaranteed to be processed in any particular order, and SELECT @variable = column_value FROM table queries, which AFAIK are guaranteed to be done in the order set by the ORDER BY clause (if present).

Try this

USE Pubs
GO

DECLARE @vchTitle VARCHAR(8000)
SET @vchTitle = ''
SELECT @vchTitle = @vchTitle + ',' + Title
FROM Titles
ORDER BY LEFT(Title,3)

SELECT @vchTitle

Roji. P. Thomas
http://toponewithties.blogspot.com

Well, let me stop defending [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />I stand corrected on two things.<br /><br />1. As per the execution plan, variable assignment occurs <b>AFTER</b> sort.<br /><br />2. As per <a href='"http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx"' target='_blank' title='"http://blogs.msdn.com/sqltips/archive/2005/07/20/441053.aspx"'>Conor</a>, For backwards compatibility reasons, SQL Server provides support for assignments of type SELECT @p = @p + 1 ... ORDER BY at the top-most scope.<br /><br />Also, I was unable to produce a repro that does not respect the ORDER BY, even though I tried hard. [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br /><br />Roji. P. Thomas<br /<a target="_blank" href=http://toponewithties.blogspot.com>http://toponewithties.blogspot.com</a><br />