Combining two mysql queries

Question

I have two sql queries...

set  @count:=0;
select @count:=@count+1 as SNO, col1, col2 FROM table;

I want to combine above queries into a single query. Any Help?

if you're initializing @count to 0 and then simply incrementing it by 1, why not just set it to 1 in the first place? — nageeb, Sep 05 '12 at 05:29

John Woo · Accepted Answer · 2012-09-05T05:43:45.713

2

You can simply do this,

select @count:=@count+1 as SNO, col1, col2 
FROM table, (SELECT @count:=0) r ;

Just like adding RowNumber for each row

select @rownum:=@rownum+1 ‘rank’, 
       p.* 
from player p, (SELECT @rownum:=0) r 
order by score 
desc limit 10;

edited Sep 05 '12 at 05:43

answered Sep 05 '12 at 05:29

John Woo

score 2 · Answer 2 · edited May 23 '17 at 12:04

2

As per my understanding,you are looking for Row_Number function in this case. If this is correct, please have a look here

e.g.

Select @count := @count + 1 As SNO, col1, col2
From table ,(SELECT @count:=0) foo

may help

Also you can refer ROW_NUMBER, Partition, and Over in MySQL for more understanding on the same

edited May 23 '17 at 12:04

Community

answered Sep 05 '12 at 05:32

Niladri Biswas

score 1 · Answer 3 · answered Sep 05 '12 at 05:31

1

Combining two queries..

SELECT t1.field1, t1.field2, t2.field1
FROM (query1) as t1, (query2) as t2
WHERE t1.field1= t2.field1

Hope this will works ...

answered Sep 05 '12 at 05:31

Basith

score 0 · Answer 4 · answered Sep 05 '12 at 06:21

0

select @count:=@count+1 as SNO, col1, col2 FROM table, (SELECT @count:=0) t;

answered Sep 05 '12 at 06:21

Ashwini Agarwal

4 Answers4