CODEWITHSUNDEEP
mysqlsqlrankrow-number
R5498
R5498

Reputation: 154

New select column with a counter that increase depending on conditions in SQL

I need to create a new column dinamically in SQL with a counter depending on certain conditions

i.e.

ID ||| COLUMN1 ||| COLUMN2 ||| COLUMN3 ||| COLUMN 4 |||NEW_COLUMN
1        null        null        null         12    
2         13         null        null         18           1
3        null        null        null         82            
4        null        null        null         81           
5        null        null        31           89           2
6        null        22          32           91           3
7        null        92          null         95           4

The counter is going to increase only when COLUMN1 or COLUMN2 or COLUMN 3 != null

The sentence would be something like:

SELECT COLUMN1,COLUMN2,COLUMN3,COLUMN4, (SELECT .....) FROM ... WHERE ...

I don't know if that's possible or if that's the correct way to face this problem in terms of efficiency. Other possibilities are doing that with java or with Jasper Reports, but i think that SQL is the easiest way to do that

Any advise is welcome.

Thank you

Upvotes: 0

Views: 847

Answers (2)

Joe G Joseph
Joe G Joseph

Reputation: 24076

another way 

select a.ID,a.COLUMN1,a.COLUMN2,a.COLUMN3,a.COLUMN4,COUNT(b.ID) from
(select * from <your_table> where coalesce(column1,column2,column3) is not null)a
join 
(select * from <your_table> where coalesce(column1,column2,column3) is not null)b
on a.ID>=b.ID
group by a.ID,a.COLUMN1,a.COLUMN2,a.COLUMN3,a.COLUMN4 
union all
select *,null from <your_table> where coalesce(column1,column2,column3) is null
order by id

Upvotes: 0

juergen d
juergen d

Reputation: 204854

select t.*, (@row := case 
                     when column1 is not null or column2 is not null or column3 is not null 
                     then @row
                     else null
            end) as NEW_COLUMN,
            (@row := case 
                     when column1 is not null or column2 is not null or column3 is not null 
                     then @row + 1 
                     else @row 
            end) as TEMP_COLUMN
from your_table t, (select @row := 0) r

I used a temp_column as helper.

Upvotes: 1

Related Questions