Link to home
Start Free TrialLog in
Avatar of indy500fan
indy500fan

asked on

I want the reverse of a TOP clause...

Friends,

I need to modify the following query so that I exclude the TOP 9 from the returned result set:

Select * From
Qualifications
Where Status = 101
Order By QualifyingTime DESC

Is it possible?  If so, how?

Thanks in advance!

Eric
ASKER CERTIFIED SOLUTION
Avatar of ralmada
ralmada
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
do you have a primary key?
if yes, it would be:

Select * From
Qualifications
Where Status = 101
  and pk not in ( select top 10 pk from Qualifications where status = 101 Order By QualifyingTime DESC )
Order By QualifyingTime DESC

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of indy500fan
indy500fan

ASKER

ralmada, I like the simplicity of your solution, but apparently I cannot do order by's in this type of solution.
I've never heard of the except clause.  Need to look at that more.

angelIII, I do not have a primary key per se.  Your second solution does seem to return the same result set as aneeshattingal.

Both are very interesting solutions that taught me something.  You would all mind sharing points?
>>but apparently I cannot do order by's in this type of solution<<
Have you tried it at all? It does work
ralmada,

I am using SQL 2005 and it gives me the following error:

Msg 1033, Level 15, State 1, Line 1
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
oops, this will solve the problem
select * from (Select top 100 percent * From Qualifications Where Status = 101 Order By QualifyingTime DESC) a 
except 
select * from (Select top 9 * From Qualifications Where Status = 101 Order By QualifyingTime DESC) b

Open in new window

Actually this should do it

select * from Qualifications Where Status = 101 Order By QualifyingTime DESC
except  
select * from (Select top 9 * From Qualifications Where Status = 101 Order By QualifyingTime DESC) b

or 

select * from (Select top 99.9 percent * From Qualifications Where Status = 101 Order By QualifyingTime DESC) a  
except  
select * from (Select top 9 * From Qualifications Where Status = 101 Order By QualifyingTime DESC) b

Open in new window

ralmada,

Yep that works!

Okay, all three work equally well and I learned something different from all three.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Select Top 1 * From
Qualifications
Where Status = 101
Order By QualifyingTime
Thank you all for your most ingenious contributions!