#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    South East of England
    Posts
    11
    Rep Power
    0

    Question Ordering Problem


    Hi,

    I'm having a problem with a SQL statement in a stored procedure, I am building the statement in the procedure because I want to vary the 'order by' clause. In effect I want the top ranking 5 employees to stay the same regardless of the order by. At the moment the statement is ordering the returned data and taking the top 5 out of that list rather than getting the top 5 and then ordering those results.

    e.g.
    select top 5 employee, post, rank, date, salary from sales where rank <> 0 and year=2002 order by salary

    any ideas welcome.

    Robin Bailey
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    not sure which field you want the top 5 of, whether asc or desc, and which final order you want...

    ... but you should be able to figure it out from this:
    Code:
    select employee
         , post
         , rank
         , date
         , salary
      from (
           select top 5 
                  employee
                , post
                , rank
                , date
                , salary 
             from sales
            where rank <> 0 
              and year=2002 
           order 
               by rank desc
           ) as derivedtable
    order
        by salary desc
    rudy
    http://r937.com/

IMN logo majestic logo threadwatch logo seochat tools logo