sql server variable increment in query

Question

In MySQL I can do something like this:

SELECT @row := @row + 1 as num FROM
(select 0 union all select 1) t1,
(select 0 union all select 1) t2,
(select 0 union all select 1) t3,
(select @row := 0) as t4;

The output of which is:

I tried to do this in sql server, but met many road blocks:

First I tried this:

SELECT * FROM
(select 0 union all select 1) t1,
(select 0 union all select 1) t2,
(select 0 union all select 1) t3;

and received:

Msg 8155, Level 16, State 2, Line 7
No column name was specified for column 1 of 't1'.
Msg 8155, Level 16, State 2, Line 8
No column name was specified for column 1 of 't2'.
Msg 8155, Level 16, State 2, Line 9
No column name was specified for column 1 of 't3'.

So I did this:

SELECT * FROM
(select 0 as f union all select 1) t1,
(select 0 as f union all select 1) t2,
(select 0 as f union all select 1) t3;

Why do I have to specify a name for column 1 on the derived tables?

Next, I tried to setup a scalar, I guessed I had to do it this way:

DECLARE @row as int
SET @row = 0

I can do this:

SELECT @row = @row + 1

Which results in nothing back until I do SELECT @row which now shows a 1

I cannot do (as I was in MySQL at the start):

DECLARE @row as int
SET @row = 0

SELECT @row = @row + 1 as num FROM
(select 0 as f union all select 1) t1,
(select 0 as f union all select 1) t2,
(select 0 as f union all select 1) t3;

I get:

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'as'.
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 't1'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 't2'.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 't3'.

How do I mimic the behavior of the first query (from MySQL) in sql server?

Joachim Isaksson · Accepted Answer · 2012-10-11 21:04:53Z

2

Well, if you just want the row number, you could just use ROW_NUMBER() in TSQL;

SELECT ROW_NUMBER() OVER (ORDER BY t1.f) num FROM
  (SELECT 0 AS f UNION ALL SELECT 1) t1,
  (SELECT 0 AS f UNION ALL SELECT 1) t2,
  (SELECT 0 AS f UNION ALL SELECT 1) t3;

That will return the row number for each combination. ROW_NUMBER() requires an ORDER BY, so just order by the first convenient column.

SQLFiddle here.

answered Oct 11, 2012 at 21:04

Joachim Isaksson

182k28 gold badges297 silver badges307 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Slippery Pete · Accepted Answer · 2012-10-11 21:05:22Z

1

If I understand the intent, and if you have SQL Server 2005 or above, try this:

select row_number() over(order by t1.f) as num from
(select 0 as f union all select 1) t1,
(select 0 as f union all select 1) t2,
(select 0 as f union all select 1) t3,
(select 0 as f) as t4;

Yields:

num
--------------------
1
2
3
4
5
6
7
8

answered Oct 11, 2012 at 21:05

Slippery Pete

3,1101 gold badge16 silver badges17 bronze badges

Comments

Mark Kremers · Accepted Answer · 2012-10-12 09:24:01Z

0

Well, for this little query :

DECLARE @row as int
SET @row = 0

SELECT @row = @row + 1 as num FROM
(select 0 as f union all select 1) t1,
(select 0 as f union all select 1) t2,
(select 0 as f union all select 1) t3;

you try to give a name to variable, so setting AND outputting is what you want, that is not possible. you have to do the first or the last, not both..

DECLARE @row as int
SET @row = 0

SELECT @row = @row + 1 FROM
(select 0 as f union all select 1) t1,
(select 0 as f union all select 1) t2,
(select 0 as f union all select 1) t3;

SELECT @row as num

answered Oct 12, 2012 at 9:24

Mark Kremers

1,79712 silver badges21 bronze badges

Collectives™ on Stack Overflow

sql server variable increment in query

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related