?
Solved

SQL SELECT NOT IN

Posted on 2010-09-11
13
Medium Priority
?
697 Views
Last Modified: 2012-05-10
I'm trying figure out how to write a SELCET statement using NOT IN TOP 10

My first statement:
SELECT TOP 10*
FROM table1.Comments, table2.Profile
WHERE table1.Comments.VID ='1' AND table1.Comments.MID = table2.Profile.MemberId
ORDER BY DateAdded DESC

For my statement using NOT IN I want to show all records NOT IN the TOP 10 but I just can't figure it out.  This is one of many examples I've tried

SELECT *
FROM table1.Comments, table2.Profile
WHERE table1.Comments.VID ='1' AND table1.Comments.MID = table2.Profile.MemberId
NOT IN (SELECT TOP 10*
FROM table1.Comments, table2.Profile
WHERE table1.Comments.VID ='1' AND table1.Comments.MID = table2.Profile.MemberId
ORDER BY DateAdded DESC)
ORDER BY DateAdded DESC

Error Message:
Incorrect syntax near the keyword 'NOT'.
Incorrect syntax near the keyword 'ORDER'.
0
Comment
Question by:swaggerking
[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
  • 3
  • 3
  • 2
  • +4
13 Comments
 
LVL 1

Expert Comment

by:z_alex
ID: 33655606
try to NOT EXIST
0
 
LVL 1

Expert Comment

by:z_alex
ID: 33655618
SELECT *
FROM table1.Comments, table2.Profile
WHERE table1.Comments.VID ='1' AND
table1.Comments.MID NOT IN (SELECT TOP 10 ID
FROM table1.Comments, table2.Profile
WHERE table1.Comments.VID ='1' AND table1.Comments.MID = table2.Profile.MemberId
ORDER BY DateAdded DESC)
ORDER BY DateAdded DESC

or

SELECT *
FROM table1.Comments
where table1.Comments.MID not in ("A", "B", "C")

or

SELECT *
FROM table1.Comments
where table1.Comments.MID not in (select top 10 ID from table1 )
0
 
LVL 4

Expert Comment

by:antonybrahin
ID: 33655642
u need to add more columns with conditions if u need to filter exactly those records
SELECT * 
FROM table1.Comments, table2.Profile
WHERE table1.Comments.VID ='1' AND table1.Comments.MID = table2.Profile.MemberId 
AND table1.Comments.VID
NOT IN (SELECT TOP 10 table1.Comments.VID  
FROM table1.Comments, table2.Profile
WHERE table1.Comments.VID ='1' AND table1.Comments.MID = table2.Profile.MemberId
ORDER BY DateAdded DESC)
ORDER BY DateAdded DESC

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33655714
SELECT * from
(
SELECT *, rn=row_number() over (ORDER BY DateAdded DESC)
FROM table1.Comments, table2.Profile
WHERE table1.Comments.VID ='1' AND table1.Comments.MID = table2.Profile.MemberId
) X
where rn > 10
ORDER BY DateAdded DESC


I'm not actually sure how you get table.column.column? but you get the idea from the statement.
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 33655717
SELECT Table1.Comments.*, Table2.Profile.*
FROM Table1.Comments
INNER JOIN Table2.Profile on (Table1.Comments.MID = Table2.Profile.MemberId
                              AND Table1.Comments.VID = '1')
WHERE Table1.Comments.MID NOT IN --Use the field you are trying to exclude
            (SELECT TOP 10 Table1.Comments.MID
             FROM Table1.Comments
             INNER JOIN Table2.Profile on (Table1.Comments.MID = Table2.Profile.MemberId
                                       AND Table1.Comments.VID = '1')
           ORDER BY DateAdded DESC)
ORDER BY DateAdded DESC

--try where not exists
SELECT Table1.Comments.*, Table2.Profile.*
FROM Table1.Comments
INNER JOIN Table2.Profile on (Table1.Comments.MID = Table2.Profile.MemberId
                              AND Table1.Comments.VID = '1')
WHERE NOT EXISTS (SELECT TOP 10 Table1.Comments.MID
                              FROM Table1.Comments
                              INNER JOIN Table2.Profile on (Table1.Comments.MID = Table2.Profile.MemberId
                                       AND Table1.Comments.VID = '1')
                             ORDER BY DateAdded DESC)
ORDER BY DateAdded DESC
0
 
LVL 5

Expert Comment

by:Priya Perumpilavil
ID: 33655849
just try the query like this
select * from inmast where mapcd_k not in (select top 10 mapcd_k from inmast)
0
 
LVL 3

Expert Comment

by:jcparedes
ID: 33659738
An easier way:

SELECT *
FROM table1.Comments, table2.Profile
WHERE table1.Comments.VID ='1' AND table1.Comments.MID = table2.Profile.MemberId

MINUS

(SELECT TOP 10*
FROM table1.Comments, table2.Profile
WHERE table1.Comments.VID ='1' AND table1.Comments.MID = table2.Profile.MemberId
ORDER BY DateAdded DESC)
ORDER BY DateAdded DESC

Cheers
0
 
LVL 3

Accepted Solution

by:
jcparedes earned 1000 total points
ID: 33659742
Sorry, made a mistake, is like this:

An easier way:

SELECT *
FROM table1.Comments, table2.Profile
WHERE table1.Comments.VID ='1' AND table1.Comments.MID = table2.Profile.MemberId
ORDER BY DateAdded DESC

MINUS

(SELECT TOP 10*
FROM table1.Comments, table2.Profile
WHERE table1.Comments.VID ='1' AND table1.Comments.MID = table2.Profile.MemberId
ORDER BY DateAdded DESC)

Cheers
0
 

Author Closing Comment

by:swaggerking
ID: 33667405
Thanks, Worked like a charm after I replaced "MINUS" with "EXCEPT".  
0
 
LVL 3

Expert Comment

by:jcparedes
ID: 33667417
Hehehe, sorry about that, too much Oracle I guess
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33667450
Is EXCEPT actually easier than row_number()?
I strongly suspect row_number will perform better.
http:#a33655714
0
 

Author Comment

by:swaggerking
ID: 33667543
cyberkiwi:
You may be right. I'm very much a novice at SQL and it's all trial and error with me. I did go through the suggestions for the past day and half without much success on my part and with a few less hairs on the head. I always appreciate experts, like yourself, for helping me out/pointing me in the right direction.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33667591
I may have a mistype in there somewhere if it didn't work for you.
Just an explanation:

EXCEPT is great, except (no pun intended) it works by
(1) first selecting two sets, one the full set and again the unwanted (in this case top 10)
(2) then comparing field for field for exact duplicates.

Row_number() assigns a row number while in the process of collecting the data, just once.
The criteria for row numbering is given in the "order by" clause, in this case "order by DateAdded DESC" so the latest DateAdded gets number 1 etc.

If your exception list is long (100 or 1000+) or the table has many columns, the comparison operation will start to show a lag.

Regards
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

741 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