Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using the result of a 'previous' column in a SELECT statement

T-SQL appears to allow us to use the value of a column in an expression in another column, going left-to-right. E.g:

declare @a int, @b int;
select @a = 2, @b = @a * 3;
select @a, @b;

yields {2, 6}.

I can't find any reference to this 'feature' in BOL etc. My question is, is this known, dependable behaviour?

like image 255
Rik Bradley Avatar asked Jul 23 '19 08:07

Rik Bradley


People also ask

Is there a way to access the previous row value in a select statement?

In other words, by using the LAG() function, from the current row, you can access data of the previous row, or the row before the previous row, and so on. The LAG() function can be very useful for comparing the value of the current row with the value of the previous row.

How do I select a previous record in SQL?

1) You can use MAX or MIN along with OVER clause and add extra condition to it. The extra condition is "ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING" which will fetch previous row value. Check this: SELECT *,MIN(JoiningDate) OVER (ORDER BY JoiningDate ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS EndDate.

How do I get previous and next record in SQL?

You can use UNION to get the previous and next record in MySQL. Insert some records in the table using insert command. Display all records from the table using select statement.

What is the result of a select statement?

The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set.


2 Answers

This is actually covered as a warning in Variables (Transact-SQL):

Warning

If there are multiple assignment clauses in a single SELECT statement, SQL Server does not guarantee the order of evaluation of the expressions. Note that effects are only visible if there are references among the assignments.

(Emphasis mine)

SQL server does assign the values one variable at a time, so in this (simple) example, SQL Server has assigned the value of @a prior to evaluating (and assigning) the expression @a * 3 to @b.

As the warning says though, don't expect the behaviour to always be the same. If you need to guarantee that assignment is in a strict order, do the assignments as separate statements:

DECLARE @a int,
        @b int;
SET @a = 2;
SET @b  = @a * 3;
SELECT @a, @b;

Edit: This behaviour does not exist when using expressions that are not assignments. For example the following statement will fail with the error "Invalid column name 'a'.":

SELECT 2 AS a,
       a * 3 AS b;

The behaviour you are describing only happens for assignment statements. You cannot reference a column by it's alias in the same SELECT it was defined.

like image 189
Larnu Avatar answered Oct 16 '22 12:10

Larnu


There is a warning in the docs that reads:

If there are multiple assignment clauses in a single SELECT statement, SQL Server does not guarantee the order of evaluation of the expressions. Note that effects are only visible if there are references among the assignments.

It means there is no guarantee that it will evaluate the expression left-to-right. For this code:

declare @a int, @b int;
select @a = 2, @b = @a * 3;
select @a, @b;

The result could be 2, 6 (@a = ... evaluated first) or 2, NULL (@b = ... evaluated first).

like image 26
Salman A Avatar answered Oct 16 '22 14:10

Salman A