EXCEPT statement error

Question

I do not understand why this query is not working:

SELECT product.maker, product.model, product.type FROM product EXCEPT
(Select top 3 with ties product.maker, product.model, product.type  
FROM Product ORDER BY model DESC)

The database scheme consists of four tables:

Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)

UPDATE: I actually tried inputting this code using mssql in the online site im in, but it still does not work. Could it be that its a possible site error (ie. something wrong with their DBMS). * NOT EXIST also does not seem to work, however I have used it before in other query questions on the site.

possible duplicate of [How do I Handle Ties When Ranking Results in MySQL?](http://stackoverflow.com/questions/2474390/how-do-i-handle-ties-when-ranking-results-in-mysql) — Déjà vu, Jun 06 '14 at 04:55
dumbest comment ever just because my code has ties doesnt mean its the same question -_- — user3681831, Jun 06 '14 at 05:35

Ankit Bajpai · Answer 1 · 2014-06-06T05:01:10.010

0

Yoy can try this query as well

SELECT product.maker, product.model, product.type FROM product 
    MINUS
Select top 3 with ties product.maker, product.model, product.type  
FROM Product ORDER BY model DESC.

Hope by this you can achieve desired result.

edited Jun 06 '14 at 05:01

answered Jun 06 '14 at 04:19

Ankit Bajpai

13,128
4
25
40

Hi, i tired running your code but i got: Incorrect syntax near the keyword 'Select' – user3681831 Jun 06 '14 at 04:24
The code may be wrong coz i dont have your schema. but the Concept is right that mysql dosent support EXCEPT kayword so you have to use another method than that. – Ankit Bajpai Jun 06 '14 at 04:30
ya, i actually included the schema but someone deleted it. NOw i added it again, i dont think anything is wrong with the schema part – user3681831 Jun 06 '14 at 04:32
still doesnt work... i find it strange that the previous NOT EXIST query didnt work – user3681831 Jun 06 '14 at 05:41

score 0 · Answer 2 · answered Jun 06 '14 at 08:27

EXCEPT and TOP are mssql (t-sql) keywords. Do you use MSSQL or MySQL as mentioned in tags to your question. If you try to run this query on MySQL the it is the reason of error because MySQL doesn't support these keywords.

In MySQL try this equivalent:

SELECT DISTINCT 
         product.maker, 
         product.model, 
         product.type FROM product 
         LEFT JOIN 
            (SELECT DISTINCT product.maker, product.model, product.type  
              FROM Product 
              ORDER BY model DESC
              LIMIT 3
             ) as p2
             ON product.maker=p2.maker
                AND product.model = p2.model
                AND product.type = p2.type
          WHERE p2.maker IS NULL
                AND p2.model IS NULL
                AND p2.type IS NULL

Note: I've added DISTINCT to the main query to emulate EXCEPT:

EXCEPT returns any distinct values from the left query that are not also found on the right query

And also added DISTINCT into the subquery to emulate TOP WITH TIES

thanks, I actually do not know if im using mysql or sql. Im actually doing online queries to problems...i think the site runs both mysql and sql. I am actually wondering why EXCEPT and NOT EXIST do not work. UPDATE: IN the site, it also lets me pick which dbms to use, it also doesnt work with mssql for some reason :( — user3681831, Jun 06 '14 at 14:42

EXCEPT statement error

2 Answers2