[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Select ALL records other than the first 10

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
Aleks
Asked:
Aleks
3 Solutions
 
AleksAuthor Commented:
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
 
FriedTyGuyCommented:
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
 
SeanStricklandCommented:
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
 
chaauCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now