Solved

Select ALL records other than the first 10

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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…
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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

808 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