Select Top 1 field and assign to local variable

SqlSql Server

Sql Problem Overview


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)

Sql Solutions


Solution 1 - Sql

why not use this:

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

Solution 2 - Sql

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

Solution 3 - Sql

Use this:

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

Solution 4 - Sql

Try this simply

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionSteve StapleView Question on Stackoverflow
Solution 1 - SqlBrett SchneiderView Answer on Stackoverflow
Solution 2 - SqlAlex K.View Answer on Stackoverflow
Solution 3 - SqlJesurajaView Answer on Stackoverflow
Solution 4 - SqlKrushnaKPawarView Answer on Stackoverflow