Temporary variable assignment / increment in select query

Question

In MySQL you are allowed to do something like this

SELECT @n := @n + 1 n,
       first_name, 
       last_name
  FROM table1, (SELECT @n := 0) m
 ORDER BY first_name, last_name

*(Codeblock was originally taken from https://stackoverflow.com/a/16555527/2279200)

Is there any equivalent approach in Oracle or SQL Server

Note:

Something similar can be done with update in SQL Server, but I am asking if it can be done with select.
Using row_number is not an option, since I want to handle how the values of @n are changing.
I am aware that SQL Server disallows having both temp variables and table column in select statement.

*... since I want to handle how the values of @n are changing* this is not clear, at least not to me. What do you actually want to do with `@n`? — Giorgos Betsos, Sep 15 '17 at 10:08
@GiorgosBetsos I mean that I do not want to increase by `1` as it is in the example. E.g. I may add a control block, in the assignment of `@n`, if the last_name start with 'f'. I do not have a real-life example right now, I am just exploring the possibilities. — Athafoud, Sep 15 '17 at 10:14
You almost certainly still want to use `row_number`, but without a concrete example of what you want, nobody can answer this. — DavidG, Sep 15 '17 at 10:20

Gordon Linoff · Answer 1 · 2017-09-15T10:26:05.860

1

You would use ROW_NUMBER():

SELECT row_number() over (order by first_name, last_name)
       first_name,last_name
FROM table1
ORDER BY first_name, last_name;

This is not just "another way" of doing this. ROW_NUMBER() is the ANSI-standard method for implementing this functionality -- and you should be learning about other ranking and window/analytic functions. The use of variables is a hack in MySQL.

This is only the simplest method of a window function. You need to learn about the other ranking functions (RANK() and DENSE_RANK()). You need to learn about PARTITION BY. You need to learn about conditional aggregation/FILTER. I have never used a database with window functions and thought: "Gosh, I wish I could use variables instead". I have often thought the opposite.

edited Sep 15 '17 at 10:26

answered Sep 15 '17 at 10:08

Gordon Linoff

1,242,037
58
646
786

Thanks for the answer. As I said `Using row_number is not an option`, see my comment above on how I want to manipulate the `@n` variable. – Athafoud Sep 15 '17 at 10:15
@Athafoud . . . Ask another question and provide sample data and desired results. That statement is not clear. – Gordon Linoff Sep 15 '17 at 10:17

score 0 · Answer 2 · answered Sep 15 '17 at 10:18

0

If you're going to use variables you must declare them first:

    DECLARE @n int
    SET @n = '...'

answered Sep 15 '17 at 10:18

Ryan Gadsdon

2,272
4
31
56

score 0 · Answer 3 · answered Sep 15 '17 at 10:39

Using row_number is not an option, since I want to handle how the values of @n are changing.

Oracle - Use a recursive sub-query factoring clause:

WITH ordered_names ( rn, first_name, last_name ) AS (
  SELECT ROW_NUMBER() OVER ( ORDER BY first_name, last_name ),
         first_name,
         last_name
  FROM   table1
),
indexed_names ( rn, n, first_name, last_name ) AS (
  SELECT rn, 1, first_name, last_name FROM ordered_names WHERE rn = 1
UNION ALL
  SELECT o.rn,
         i.n + 1, -- Your logic here,
         o.first_name,
         o.last_name
  FROM   ordered_names o
         INNER JOIN
         indexed_names i
         ON ( o.rn = i.rn + 1 )
)
SELECT n, first_name, last_name
FROM   indexed_names

Temporary variable assignment / increment in select query

3 Answers3