How to set value to variable in select query in SQL Server 2012?

Question

I want to generate a sequence for a complex query. For that purpose I have used a variable @rowNo.

My logic is :- If a field - isremoved = 0 then increase row number by 1.
If isremoved = 1 then increase row number by 1 only once till you find next 0.

This is so far I have done, but it gives me syntax error.

DECLARE @rowNo INT;
SET @rowNo = -1;

SELECT
case when sampleTable.isremoved = 0 then @rowNo + 1
    else @rowNo end
as rowNumber,
X,
Y,
Z
.
.
FROM tbl_sample sampleTable
INNER JOIN tbl_sample_2 sample2 ON sampleTable.id = sample2.id
.
.
.

This is my desire output :-

So what is the right way to achieve this kind of functionality in sql server 2012 ?

EDIT :- I do have one solution to use sub query to retrieve row number. But that will hit performance as it is very complex query (more than 20 joins) with huge amount of data. So please suggest me an alternative.

Have a look at this: http://stackoverflow.com/questions/20232641/add-a-row-number-to-result-set-of-a-sql-query — markpsmith
– markpsmith, Commented Oct 12, 2015 at 14:06
I apologize for confusion. I have updated question. Image represents my outcome perfectly in case you are still confused. — unknown
– unknown, Commented Oct 12, 2015 at 14:17

Gordon Linoff · Accepted Answer · 2015-10-12 14:09:18Z

1

In SQL Server 2012+, you can do what you want with a cumulative sum:

SELECT sum(case when sampleTable.isremoved = 0 then 1 else 0 end) over
            (order by . . .)
       . . .

The order by should repeat the order by in the outer query. You can also try using order by (select null)). In my experience, this uses the ordering of the data in the outer query, but this is not documented to always work.

SQL Server does not allow you to set variables in a SELECT and to return values in a result set. One or the other, but not both.

answered Oct 12, 2015 at 14:09

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Charles Bretana · Accepted Answer · 2015-10-12 14:13:29Z

1

one way to do this is with subquery:

Select (Select count(*) from tbl_sample
        where id <= a.id 
            and a.isRemoved =1) rowNumber,
   X, Y, Z
From tbl_sample a
     Join tbl_sample_2 b 
         On b.id = a.id

edited Oct 12, 2015 at 14:13

answered Oct 12, 2015 at 14:06

Charles Bretana

147k24 gold badges158 silver badges219 bronze badges

2 Comments

unknown Over a year ago

It is very complex query (almost 20 joins). So This kind of solution hit performance.

Charles Bretana Over a year ago

Yes, it will, but, assuming one of the id columns is a Foreign Key, if you put an index on it, that will minimize the performance hit.

Giorgos Betsos · Accepted Answer · 2015-10-12 14:20:50Z

1

I assume your rowNumber logic is equivalent to: if previous isremoved = 0 then increment rowNumber by 1, otherwise keep it as it is.

Using a combination of LAG and SUM ... OVER you can easily implement this logic:

SELECT id, isremoved,
       SUM(IIF(prevFlag = 0, 1, 0)) OVER (ORDER BY id) AS rowNumber
FROM (
SELECT a.id, isremoved,
       COALESCE(LAG(isremoved) OVER (ORDER BY a.id), isremoved) AS prevFlag
FROM tbl_sample AS a
INNER JOIN tbl_sample_2 AS b ON b.id = a.id) AS t

Demo here

answered Oct 12, 2015 at 14:20

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

10 Comments

unknown Over a year ago

Your solution is right for POC. But it does not work with my actual query. I will get back with exact question soon.

unknown Over a year ago

This is the right solution. But there is one issue, I am using order by. That changes row number. Demo here - sqlfiddle.com/#!6/5a30f/2

Giorgos Betsos Over a year ago

@unknown I'm not sure I understand what your actual requirement is. If your rows are ordered by seq instead of id then use this field in the OVER clause of the window functions instead of id.

unknown Over a year ago

I just want to apply 'order by' on result. Do not confuse with the name seq. Lets assume that there is a name field I am trying to order by. The same would happen.

Giorgos Betsos Over a year ago

@unknown This is not possible. It's like wanting to order a single field in two different ways at the same time.

|

Collectives™ on Stack Overflow

How to set value to variable in select query in SQL Server 2012?

3 Answers 3

Comments

2 Comments

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related