-2

I have a Cars table and used this query to select a row with MaxSpeed = 200 condition:

SELECT TOP 1 * 
FROM Cars
WHERE MaxSpeed = 200
ORDER BY [ID] DESC;

This query works very well, but now I need to store this row ID to a variable. I can declare an int variable, but I don't know how can get this row ID.

DECLARE @id AS INT;
Behdadsoft
  • 77
  • 9
  • 1
    `TOP 1 ORDER BY [ID] DESC` is equivalent to `SELECT MAX([ID]) FROM Cars . . .` – Squirrel Aug 09 '23 at 04:13
  • 1
    @Squirrel: **except** two points: **(1)** you need a `GROUP BY` when using `MAX(ID)` in your `SELECT`, and **(2)** if there are two or more entries with the same `MaxSpeed`, the `MAX(ID)` approach might return **multiple rows** (while `SELECT TOP (1)` will only return a single row). You cannot handle assignment to a variable when multiple rows are returned .... – marc_s Aug 09 '23 at 04:38
  • Should be flagged as a duplicate of which there are many e.g https://stackoverflow.com/questions/3974683/how-to-set-variable-from-a-sql-query – Dale K Aug 09 '23 at 07:00

1 Answers1

3

Just simply assign the value from the SELECT query to your variable:

DECLARE @id AS INT;

SELECT TOP 1 
    @id = ID
FROM Cars
WHERE MaxSpeed = 200
ORDER BY [ID] DESC;

SELECT @id;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459