Ordering Problem - Dev Shed

Dev Shed Forums Sponsor:

September 5th, 2003, 08:56 PM

Robin Bailey

Junior Member

Join Date: Sep 2003

Location: South East of England

Posts: 11

Time spent in forums: < 1 sec
Reputation Power: 0

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

September 8th, 2003, 11:09 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,369

Time spent in forums: 3 Months 1 Week 2 Days 6 h 18 m 49 sec
Reputation Power: 4140

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/

Viewing: Dev Shed Forums > Databases > MS SQL Development > Ordering Problem

« Previous Thread | Next Thread »

Developer Shed Advertisers and Affiliates

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread:

Posting Rules
You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off

View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox

Forum Jump