[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How do I select all records except the first 5 records with the highest rank number

Posted on 2011-05-08
Medium Priority
Last Modified: 2012-05-11
I have 4 fields in my table (picture_contests).

1. record_ID
2. rank
3. picture_file
4. title

I would like to pull all records except the first 5 records that are ranked the highest.  The rank value is out of a 100 with 100 being the highest.



Question by:brihol44
1 Comment
LVL 21

Accepted Solution

Dale Burrell earned 1000 total points
ID: 35716654
select *
from Picture_Contest
where id not in
  select top 5 id from Picture_Contest order by Rank asc

The only problem with this is that its non-deterministic, i.e. if any of the top 5 records share the same rank then it will be selecting them at random and it could change from query to query. Ideally you'd have additional information to ensure that the top 5 records are consistent.

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question