Query with Exist

Question

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

Answer 1

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

Answer 2

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];

Answer 3

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)
)

Answer 4

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