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
indy500fanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ralmadaCommented:
something like this
select * from (Select * 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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aneesh RetnakaranDatabase AdministratorCommented:
Select * From
Qualifications
Where Status = 101
and QualifyingTime  NOT IN( SELECT top 9 QualifyingTime From
Qualifications
Where Status = 101 Order By QualifyingTime DESC ) Order By QualifyingTime DESC
 
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Guy Hengel [angelIII / a3]Billing EngineerCommented:
and if you have sql 2005+, you can do this:
select * from  (
Select t.*
  , row_number() over (partition by status order by qualifyingtime desc) rn
 From Qualifications t
Where Status = 101
) sq
where sq.rn > 9

Open in new window

0
indy500fanAuthor Commented:
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?
0
ralmadaCommented:
>>but apparently I cannot do order by's in this type of solution<<
Have you tried it at all? It does work
0
indy500fanAuthor Commented:
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.
0
ralmadaCommented:
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

0
ralmadaCommented:
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

0
indy500fanAuthor Commented:
ralmada,

Yep that works!

Okay, all three work equally well and I learned something different from all three.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
note that the NOT IN () using the time field will NOT give the same result if there are ties on the 9th/10th positions, while mine will consistently skip 9 records.... you might want to consider that to choose your final solution, resp use RANK() or DENSE_RANK() function instead of ROW_NUMBER()
0
halfbloodprinceCommented:
Select Top 1 * From
Qualifications
Where Status = 101
Order By QualifyingTime
0
indy500fanAuthor Commented:
Thank you all for your most ingenious contributions!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.