SQL Server – Get Last Result of Query and Set to Variable

sql server

I am trying to get the last (most recent) record from a query and set it to the value of a declared variable, but it doesn't seem to be working.

DECLARE @PriceID int
SELECT TOP 1 log_id = @PriceID
  FROM price_logs
 WHERE part_id = 45
 ORDER BY log_id DESC

In this instance, the part_id value doesn't matter, it just need to be a value that exists within the database. When I run this code I am getting a blank value for @PriceID, yet when I check the values in the price_logs table, there is a data row to be collected. So what is it I am doing wrong here?

Best Answer

Swap the variable and column name so you are setting the value to the variable, not the other way around. How you have it written, you are setting the column name log_id to the value of @PriceID which is currently NULL since you only declared it.

DECLARE @PriceID int
SELECT TOP 1 @PriceID = log_id
  FROM price_logs
 WHERE part_id = 45
 ORDER BY log_id DESC
Related Question