Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 712
  • Last Modified:

SQL SELECT NOT IN

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
swaggerking
Asked:
swaggerking
  • 3
  • 3
  • 2
  • +4
1 Solution
 
z_alexCommented:
try to NOT EXIST
0
 
z_alexCommented:
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
 
antonybrahinCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
cyberkiwiCommented:
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
 
Ephraim WangoyaCommented:
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
 
Priya PerumpilavilSoftware EngineerCommented:
just try the query like this
select * from inmast where mapcd_k not in (select top 10 mapcd_k from inmast)
0
 
jcparedesCommented:
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
 
jcparedesCommented:
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
 
swaggerkingAuthor Commented:
Thanks, Worked like a charm after I replaced "MINUS" with "EXCEPT".  
0
 
jcparedesCommented:
Hehehe, sorry about that, too much Oracle I guess
0
 
cyberkiwiCommented:
Is EXCEPT actually easier than row_number()?
I strongly suspect row_number will perform better.
http:#a33655714
0
 
swaggerkingAuthor Commented:
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
 
cyberkiwiCommented:
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

Independent Software Vendors: 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!

  • 3
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now