set variable from querry 2

[chipCHE]

I have the following Querry which works:

Declare @DateofPurchase as datetime
SELECT TOP 1 DateofPurchase
FROM Purchases
WHERE customerID = 75
ORDER BY DateofPurchase DESC

I would like to set @DateofPurchase = DateofPurchase from the above querry which returns only one row, but cannot figure out the syntax.

I know it is a simple task and a stupid question, however, any help would be appreciated.

Thanks in advance.

 

[donutman]

Your choice of 3 that I know of:

[Blue]SET[/Blue] @DateOfPurchase[Gray]=[/Gray][Gray]([/Gray][Blue]SELECT[/Blue] [Blue]TOP[/Blue] 1 DateOfPurchase
                        [Blue]FROM[/Blue] Purchases [Blue]WHERE[/Blue] CustomerID[Gray]=[/Gray]75
                        [Blue]ORDER[/Blue] [Blue]BY[/Blue] DateOfPurchase [Blue]DESC[/Blue][Gray])[/Gray]
[Blue]SELECT[/Blue] @DateOfPurchase[Gray]=[/Gray][Gray]([/Gray][Blue]SELECT[/Blue] [Blue]TOP[/Blue] 1 DateOfPurchase
                        [Blue]FROM[/Blue] Purchases [Blue]WHERE[/Blue] CustomerID[Gray]=[/Gray]75
                        [Blue]ORDER[/Blue] [Blue]BY[/Blue] DateOfPurchase [Blue]DESC[/Blue][Gray])[/Gray]
[Blue]SELECT[/Blue] [Blue]TOP[/Blue] 1 @DateOfPurchase[Gray]=[/Gray]DateOfPurchase
                        [Blue]FROM[/Blue] Purchases [Blue]WHERE[/Blue] CustomerID[Gray]=[/Gray]75
                        [Blue]ORDER[/Blue] [Blue]BY[/Blue] DateOfPurchase [Blue]DESC[/Blue]

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[yksvaan]

Perhaps this would work too

SELECT @DateOfPurchase= MAX(DateOfPurchase)
FROM Purchases WHERE CustomerID=75

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[chipCHE]

Thank you both for helping. That was exactly what I needed.
I ended up using :
SET @DateOfPurchase=(SELECT TOP 1 DateOfPurchase
FROM Purchases WHERE CustomerID=75
ORDER BY DateOfPurchase DESC)
From Donutman.

Thank you again.

 

[donutman]

yksvaan's approach is more efficient.

[Blue]SET[/Blue] @DateOfPurchase[Gray]=[/Gray][Gray]([/Gray][Blue]SELECT[/Blue] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]DateOfPurchase[Gray])[/Gray]
                        [Blue]FROM[/Blue] Purchases [Blue]WHERE[/Blue] CustomerID[Gray]=[/Gray]75[Gray])[/Gray]

-Karl


[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]