Solved

Select ALL records other than the first 10

Posted on 2013-11-28
4
255 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

930 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

11 Experts available now in Live!

Get 1:1 Help Now