Solved

Select ALL records other than the first 10

Posted on 2013-11-28
4
259 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 25

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 Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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…

752 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