SQL Server Performance

Query

Discussion in 'General DBA Questions' started by lamth, Oct 6, 2006.

  1. lamth New Member

    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.
  2. khtan New Member


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



    KH
  3. khtan New Member

    >> 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
  4. lamth New Member

    thank so much
  5. Twan New Member

    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
  6. kazi New Member

    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
  7. Madhivanan Moderator

    That will only work in sql server 2005

    Madhivanan

    Failing to plan is Planning to fail
  8. Roji. P. Thomas New Member

    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
  9. Roji. P. Thomas New Member

    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
  10. FrankKalis Moderator

    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 Kalis
    Moderator
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
  11. Roji. P. Thomas New Member

    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
  12. Roji. P. Thomas New Member

  13. Twan New Member

    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
  14. Roji. P. Thomas New Member

    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
  15. FrankKalis Moderator

    Roji, I don't agree that this behaviour is not documented at all. Check this out:<a target="_blank" href=http://support.microsoft.com/kb/264229/en-us>http://support.microsoft.com/kb/264229/en-us</a><br />Although it doesn't explicitely mention our case here, I think one can derive this from that KB article. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  16. Adriaan New Member

    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).
  17. Roji. P. Thomas New Member

    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
  18. FrankKalis Moderator

    Roji, we're NOT talking about any kind of concatenation here. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />It's just the simple<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @vchTitle VARCHAR(75)<br />SELECT TOP 1 @vchTitle = Title<br /> FROM Titles<br />ORDER BY Title DESC<br />SELECT @vchTitle<br /><br /> <br />------------------------------ <br />You Can Combat Computer Stress<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br />Nothing more and nothing less. <br /><br />With any kind of concatenation involved, I absolutely agree that this is undocumented behaviour that might or might not work.<br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>
  19. Roji. P. Thomas New Member

    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 />

Share This Page