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
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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
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];
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)
)
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