Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Why this SQL query with EXISTS is not working?

I wrote this query to show me the last purchase of each customers using "EXISTS" but it is not working. it returns all of the rows without filtering them. Can you please advise me how to resolve this issue?
Thank you in advance

snipped image

SELECT O1.custid, O1.orderid, O1.orderdate, O1.empid 
FROM Sales.Orders AS O1
WHERE EXISTS             (
                SELECT O2.custid, MAX(O2.orderdate)
                FROM Sales.Orders AS O2
                WHERE O2.custid=O1.custid 
                GROUP BY O2.custid
             );
Tags:

2

Answers


  1. 0

    It appears that you should be joining to that exists subquery:

    SELECT O1.custid, O1.orderid, O1.orderdate, O1.empid 
FROM Sales.Orders AS O1
INNER JOIN (
    SELECT O2.custid, MAX(O2.orderdate) AS maxorderdate
    FROM Sales.Orders AS O2 
    GROUP BY O2.custid
)
    ON O2.custid = O1.custid AND
       O2.maxorderdate = O1.orderdate;

    But note that on any recent version of SQL Server, or on MySQL 8+, we can also use ROW_NUMBER() here:

    WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY custid ORDER BY orderdate DESC) rn
    FROM Sales.Orders
)

SELECT custid, orderid, orderdate, empid
FROM cte
WHERE rn = 1;
    Login or Signup to reply.
  2. 0

    In your main query you select orders. In your subquery you look for orders matching the main order’s customer ID. This gives you at least one row (because you’ll find at least the same row again). You then aggregate these rows and group them by customer ID, thus getting exactly one row. So the question whether there EXISTS a row in your subquery is always answered TRUE.

    Here are three approaches. It seems you had something like the first or second in mind:

    Get all orders where exists a match in the set of most recent orders:

    SELECT o1.custid, o1.orderid, o1.orderdate, o1.empid 
FROM Sales.Orders AS o1
WHERE EXISTS 
(
  SELECT NULL
  FROM Sales.Orders AS o2
  GROUP BY o2.custid
  HAVING o2.custid = o1.custid 
  AND MAX(o2.orderdate) = o1.orderdate
);

    This is a bit hard to read. It’s easier with IN:

    The same with an IN clause

    SELECT o1.custid, o1.orderid, o1.orderdate, o1.empid 
FROM Sales.Orders AS o1
WHERE (o1.custid, o1.orderdate) IN
(
  SELECT o2.custid, MAX(o2.orderdate)
  FROM Sales.Orders AS o2
  GROUP BY o2.custid
);

    You can also use NOT EXISTS:

    The most recent order is an order for with not exists a more recent order

    SELECT o1.custid, o1.orderid, o1.orderdate, o1.empid 
FROM Sales.Orders AS o1
WHERE NOT EXISTS 
(
  SELECT NULL
  FROM Sales.Orders AS o2
  WHERE o2.custid = o1.custid 
  AND o2.orderdate > o1.orderdate
);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search