Solved

Select ALL records other than the first 10

Posted on 2013-11-28
4
254 Views
Last Modified: 2013-11-28
I need to select all records from a table other than the last 10

SELECT  logid ,
        firmid ,
        userid ,
        fileid ,
        filetype ,
        searchdate FROM
dbo.LogSearches

The above would select all records
0
Comment
Question by:amucinobluedot
4 Comments
 

Author Comment

by:amucinobluedot
ID: 39684067
This query will display the top 10 records, in this case I need to select ALL other records BUT the ones selected with this:

SELECT  TOP 10 logid ,
        firmid ,
        userid ,
        fileid ,
        filetype ,
        searchdate FROM
dbo.LogSearches
ORDER by logid desc
0
 
LVL 1

Assisted Solution

by:FriedTyGuy
FriedTyGuy earned 167 total points
ID: 39684070
SELECT *
FROM dbo.LogSearches
WHERE logid NOT IN(
SELECT  TOP 10 logid
FROM
dbo.LogSearches
)
ORDER by logid desc

Ugly but it should work.
0
 
LVL 11

Assisted Solution

by:SeanStrickland
SeanStrickland earned 166 total points
ID: 39684184
That should do it, but make sure that your ORDER BY clause is located within the subquery to be sure that you know it's picking up the first 10 ids.

SELECT  TOP 10 logid ,
        firmid ,
        userid ,
        fileid ,
        filetype ,
        searchdate 
FROM dbo.LogSearches
WHERE logid NOT IN (SELECT TOP 10 logid FROM dbo.LogSearches ORDER BY logid desc)
ORDER by logid desc

Open in new window

0
 
LVL 24

Accepted Solution

by:
chaau earned 167 total points
ID: 39684244
Just wanted to add that there is a different approach:
1. Using CTE
;with cte as
(SELECT logid, ROW_NUMBER() OVER (ORDER BY logid) AS rn FROM dbo.LogSearches)
SELECT  l.logid ,
        l.firmid ,
        l.userid ,
        l.fileid ,
        l.filetype ,
        l.searchdate FROM
dbo.LogSearches l INNER JOIN cte on l.logid = cte.logid
WHERE cte.rn > 10

Open in new window

2. Using a subquery:
1. Using CTE
SELECT  l.logid ,
        l.firmid ,
        l.userid ,
        l.fileid ,
        l.filetype ,
        l.searchdate FROM
dbo.LogSearches l INNER JOIN (SELECT logid, ROW_NUMBER() OVER (ORDER BY logid) AS rn FROM dbo.LogSearches) AS cte on l.logid = cte.logid
WHERE cte.rn > 10

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

17 Experts available now in Live!

Get 1:1 Help Now