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

Posted on 2011-05-08
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

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    Query syntax 4 82
    SQL Column not found 7 36
    MS SQL 2005 Srink database in chunks 4 33
    Last 12 months of data 2 23
    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 article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
    This video discusses moving either the default database or any database to a new volume.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now