How to get row number using sql stored procedure

Question

I have two tables. one is local and other one is foreign. so what I want to do is to give row numbers after joining two tables using stored procedure. First I want to get same number of column from two tables and after that I want combine as a one table and give row numbers. below is my query.


set @row_number=0;

select (@row_number:=@row_number + 1) as number, 
(
select a.*
from 
(select ID,title,last_name,first_name
from local
)
a
union all
select b.*
from
(select ID,title ,last_name,first_name
from foreign
)
b
)
;

Could anyone please tell me what the wrong with it?

It's either MySQL or SQL Server. These are completely different products. Which one are you asking about? SQL Server has ranking functions like `ROW_NUMBER()`. MySQL doesn't. MySQL doesn't have most of the ranking, windowing and analytic functions offered by other databases — Panagiotis Kanavos, Sep 30 '16 at 06:51
Why do people mark questions with both MySQL and SQL Server tags when clearly they want an answer for only *one* of them? — Panagiotis Kanavos, Sep 30 '16 at 06:51
Possible duplicate of [With MySQL, how can I generate a column containing the record index in a table?](http://stackoverflow.com/questions/3126972/with-mysql-how-can-i-generate-a-column-containing-the-record-index-in-a-table) — Panagiotis Kanavos, Sep 30 '16 at 07:00
@PanagiotisKanavos - May be its because the syntax belongs to `Mysql` and they want equivalent in `Sql server` — Pரதீப், Sep 30 '16 at 07:16
There are far, far too many questions mislabeled as both `MySQL` and `sql-server`. I really want to know why so we can clean up the questions. A mislabeled questions usually gets downvoted and closed as unclear in minutes — Panagiotis Kanavos, Sep 30 '16 at 07:24
That is my misunderstanding with tags because I am a newbie to stackoverflow. thnks @panagiotis for mentioning about tags. Now I am careful with tags when I am going to post a problem. — casper, Sep 30 '16 at 09:02

Pரதீப் · Answer 1 · 2016-09-30T06:55:32.620

2

Use ROW_NUMBER window function in SQL SERVER

SELECT Row_number()
         OVER(
           ORDER BY (SELECT NULL))AS number,a.*
FROM   (SELECT ID,
               title,
               last_name,
               first_name
        FROM   local
        UNION ALL
        SELECT ID,
               title,
               last_name,
               first_name
        FROM   FOREIGN) a

Note : replace (SELECT NULL) with the column you want order by row number generation. Now the row number generation is arbitrary

edited Sep 30 '16 at 06:55

answered Sep 30 '16 at 06:50

Pரதீப்

91,748
19
131
172

1

A Row number is pretty useless without actual ordering, unless it's only meant for non-significant labelling (ie if you don't actually care about the numbers). The value will change randomly each time the query is executed, either due to parallel execution, or a changing execution plan. – Panagiotis Kanavos Sep 30 '16 at 06:53
3

Thanks @Prdp . what are the changes if we use mysql? – casper Sep 30 '16 at 07:17
@MadhushaPerera - You want answer for `Mysql` or `Sql server` – Pரதீப் Sep 30 '16 at 07:19
@MadhushaPerera MySQL and SQL Server are completely different. Besides, this question has *already* been answered for MySQL by the duplicate question. Using the *correct* tags means that you'll get an answer a lot faster – Panagiotis Kanavos Sep 30 '16 at 07:22

score 1 · Accepted Answer · edited May 23 '17 at 12:33

It seems that you are using MySQL, not SQL Server, and try to emulate row numbers, as shown eg in this duplicate question. This is trivial to do in SQL Server using the ROW_NUMBER function, as shown by @Prdp's answer.

MySQL though doesn't have the ranking, analytic or windowing functions found in other databases. Such functions can be emulated in a very limited fashion by using non-standard SQL tricks, as shown in the linked question.

Such tricks are very limited though. A typical use of ROW_NUMBER is to rank records inside a group, eg top 10 salesmen by region. It's not possible to do that with the @curRow := @curRow + 1 trick. There are performance implications as well. This trick will only work if the rows are processed sequentially.

In the question's case, a MySQL query would probably look like this:

SELECT  l.ID,
        l.title,
        l.last_name,
        l.first_name,
        @curRow := @curRow + 1 AS row_number
FROM    (   select ID,title,last_name,first_name
            from local
            UNION ALL
            select ID,title ,last_name,first_name
            from foreign
         ) l
JOIN    (SELECT @curRow := 0) r

The trick here is that JOIN (SELECT @curRow := 0) creates the variable @curRow with an initial value of 0 and returns its value. The database will take the query results and for each row, it will increase the variable and return the increased value. This can only be done at the end and forces sequential processing of the results.

By using JOIN (SELECT @curRow :=0) r you just avoid creating the variable in a separate statement.

thnks a lot @Panagiotis. your answer helped me to solve my problem — casper, Sep 30 '16 at 08:42

How to get row number using sql stored procedure

2 Answers2