Solved

SQL SELECT NOT IN

Posted on 2010-09-11
13
684 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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:ewangoya
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 250 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

Gigs: Get Your Project Delivered by an Expert

Select from 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.

Question has a verified solution.

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

Suggested Solutions

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

815 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

8 Experts available now in Live!

Get 1:1 Help Now