Select TOP 1 Into a Variable

Posted: September 15, 2011 in SQL Server 2005

You may need to select the result of the TOP command into a variable and this can be done as follows:

-- Declare variable
DECLARE @lv_value_asc VARCHAR(20);
DECLARE @lv_value_desc VARCHAR(20);

-- Create table variable
DECLARE @tbl_test TABLE
       (col_1 INT IDENTITY,
        col_2 VARCHAR(20));

-- Insert test records
INSERT INTO @tbl_test (col_2) VALUES ('A');
INSERT INTO @tbl_test (col_2) VALUES ('B');
INSERT INTO @tbl_test (col_2) VALUES ('D');
INSERT INTO @tbl_test (col_2) VALUES ('E');
INSERT INTO @tbl_test (col_2) VALUES ('F');

-- Select TOP 1 ascending value into variable
SELECT TOP 1 @lv_value_asc = tt.col_2
FROM   @tbl_test tt;

-- Select TOP 1 descending value into variable
SELECT   TOP 1 @lv_value_desc = tt.col_2
FROM     @tbl_test tt
ORDER BY 1 DESC;

-- Show variable value
SELECT @lv_value_asc  value_asc, 
       @lv_value_desc value_desc;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s