Query with Exist

CouldsInSky 41 Reputation points
2020-08-19T20:00:44.82+00:00

Hello team,
I have written
a query as below:
;With myCTETable
AS
(
SELECT ShipCountry, CustomerID, OrderID, OrderDate, Rank() Over(Partition by shipCountry Order by Shipcountry, OrderDate Asc) AS FirstOrderDate
From Orders
)

SELECT ShipCountry, CustomerID, OrderID, OrderDate, FirstOrderDate
FROM myCTETable
Where FirstOrderDate = 1;

This worked perfect.

Now I want to know what the second approach is:

I have this one:

SELECT a.ShipCountry, a.CustomerID, a.OrderID, a.OrderDate
From Orders a
WHERE Exists (Select CustomerID, Min(OrderDate) From Orders
Group by CustomerID)

The second approach doesn't work. How can I fix the second approach?

Regards,
CloudsInSky

{count} votes

4 answers

Sort by: Most helpful
  1. Tom Cooper 8,026 Reputation points
    2020-08-19T20:26:21.68+00:00

    You could do

    SELECT a.ShipCountry, a.CustomerID, a.OrderID, a.OrderDate
    From Orders a
    Cross Apply (Select Top 1 b.OrderDate From Orders b
       Where a.CustomerID = b.CustomerID Order By b.OrderDate) x
    Where a.OrderDate = x.MinDate 
    

    Tom

    No comments

  2. Guoxiong 7,681 Reputation points
    2020-08-19T21:48:26.073+00:00

    You also can use INNER JOIN to achieve your target:

    SELECT a.[OrderID]
          ,a.[OrderDate]
          ,a.[CustomerID]
          ,a.[ShipCountry]
      FROM Orders AS a
    INNER JOIN (
        SELECT [CustomerID], [ShipCountry], MIN([OrderDate]) AS [OrderDate]
        FROM Orders
        GROUP BY [CustomerID], [ShipCountry]
    ) AS b ON a.[CustomerID] = b.[CustomerID] AND a.[ShipCountry] = b.[ShipCountry] AND a.[OrderDate] = b.[OrderDate];
    
    No comments

  3. Jingyang Li 4,366 Reputation points
    2020-08-19T23:15:01.493+00:00
    SELECT a.ShipCountry, a.CustomerID, a.OrderID, a.OrderDate
    From Orders a
    WHERE Exists (Select 1 From Orders b
    WHere a.ShipCountry=b.ShipCountry
    Group by ShipCountry
    Having a.OrderDate=Min(OrderDate)
    )
    
    No comments

  4. MelissaMa-MSFT 24,116 Reputation points
    2020-08-20T02:00:05.577+00:00

    Hi CouldsInSky,

    Please find another method from below:

    SELECT  a.ShipCountry, a.CustomerID, a.OrderID, a.OrderDate
    From Orders a
    WHERE NOT EXISTS
            (
            SELECT 1
            FROM Orders
            WHERE OrderDate<a.OrderDate
            and  a.[ShipCountry] = [ShipCountry]  
            )
    

    If the response is helpful, please click "Accept Answer" and upvote it.

    Best regards
    Melissa

    No comments