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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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
Have you tried it at all? It does work
ASKER
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.
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
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
ASKER
ralmada,
Yep that works!
Okay, all three work equally well and I learned something different from all three.
Yep that works!
Okay, all three work equally well and I learned something different from all three.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Select Top 1 * From
Qualifications
Where Status = 101
Order By QualifyingTime
Qualifications
Where Status = 101
Order By QualifyingTime
ASKER
Thank you all for your most ingenious contributions!
if yes, it would be:
Open in new window