Solved

SQL SELECT NOT IN

Posted on 2010-09-11
13
686 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
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…

829 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