Solved

SQL SELECT NOT IN

Posted on 2010-09-11
13
678 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
Comment Utility
try to NOT EXIST
0
 
LVL 1

Expert Comment

by:z_alex
Comment Utility
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
Comment Utility
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
 
LVL 58

Expert Comment

by:cyberkiwi
Comment Utility
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
Comment Utility
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
Comment Utility
just try the query like this
select * from inmast where mapcd_k not in (select top 10 mapcd_k from inmast)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 3

Expert Comment

by:jcparedes
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks, Worked like a charm after I replaced "MINUS" with "EXCEPT".  
0
 
LVL 3

Expert Comment

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

Expert Comment

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

Author Comment

by:swaggerking
Comment Utility
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
Comment Utility
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

11 Experts available now in Live!

Get 1:1 Help Now