Select Top 1 field and assign to local variable

Question

I want to take the value of ExtractedDate from this query and use it as @LastExtractDate in the next query. How do I do that?

    SELECT TOP 1 [ExtractedDate]
    FROM [OnsiteV4].[dbo].[SqlPendingIndex] order by ExtractedDate desc

next query:

    insert into @table(Hex, KeyDeviceId, ObjectDateTime, ExtractedDate  )
SELECT     CONVERT(VARCHAR(MAX), CONVERT(VARBINARY(MAX), ObjectValue, 1)) AS Hex, KeyDeviceId, ObjectDateTime , GETDATE ()
    FROM         SQLPending
    WHERE     (ObjectSubType LIKE '%GAS%') and (ObjectDateTime > @LastExtractDate)

why not pick an answer after all these years haha – SketchyTurtle Feb 22 '18 at 16:02 — SketchyTurtle, Feb 22 '18 at 16:02

score 158 · Answer 1 · edited Jun 24 '14 at 12:40

158

why not use this:

declare @LastExtractDate date
SELECT TOP 1 @LastExtractDate=[ExtractedDate]
FROM [OnsiteV4].[dbo].[SqlPendingIndex] order by ExtractedDate desc

edited Jun 24 '14 at 12:40

podiluska

50,950
7
98
104

answered Jun 24 '14 at 11:12

Brett Schneider

3,993
2
16
33

2

This syntax is not correct. You will get NULL value in Sql Server if you try to run this code. You need to assign the value to the variable from a subquery. Example: `declare @SomeVar varchar(100) = (select top 1 someCol from someTable)` – HamsterWithPitchfork Oct 25 '22 at 13:33

score 24 · Answer 2 · answered Jun 24 '14 at 11:11

Simply declare & assign:

DECLARE @LastExtractDate DATETIME = (
    SELECT TOP 1 [ExtractedDate] FROM [OnsiteV4].[dbo].[SqlPendingIndex] order by ExtractedDate desc
)

or better:

DECLARE @LastExtractDate DATETIME = (
    SELECT MAX(ExtractedDate) FROM [OnsiteV4].[dbo].[SqlPendingIndex]
)

score 9 · Answer 3 · answered Jun 24 '14 at 11:15

9

Use this:

DECLARE @ExtractedDate DATETIME
SET  @ExtractedDate = (SELECT    TOP 1 ExtractedDate
                       FROM      [OnsiteV4].[dbo].[SqlPendingIndex]
                       ORDER BY  ExtractedDate DESC

answered Jun 24 '14 at 11:15

Jesuraja

3,774
4
24
48

score 5 · Answer 4 · edited Jun 24 '14 at 15:12

5

Try this simply

declare @LastExtractDate date 
SELECT @LastExtractDate=MAX([ExtractedDate])
FROM [OnsiteV4].[dbo].[SqlPendingIndex]

edited Jun 24 '14 at 15:12

Andy

49,085
60
166
233

answered Jun 24 '14 at 15:11

KrushnaKPawar

51
2

Select Top 1 field and assign to local variable

4 Answers4