CODEWITHSUNDEEP
mysqlviewrow-number
Romesh
Romesh

Reputation: 2274

I want row number in view Or variable in View

I know it is not possible directly. But I want to achieve this by any indirect method if possible.

Actually I wanted to add below query to view which throws error , Sub query not allowed in view.

select T1.Code,
       T1.month,
       T1.value,
       IfNull(T2.Value,0)+IfNull(T3.value,0) as value_begin

  from (select *,@rownum := @rownum + 1 as rownum
          from Table1 
          Join (SELECT @rownum := 0) r) T1
  left join (select *,@rownum1 := @rownum1 + 1 as rownum
               from Table1 
               Join (SELECT @rownum1 := 0) r) T2
    on T1.code = T2.code
   and T1.rownum = T2.rownum + 1
  left join (select *,@rownum2 := @rownum2 + 1 as rownum
               from Table1 
               Join (SELECT @rownum2 := 0) r) T3
    on T1.code = T3.code
   and T1.rownum = T3.rownum + 2
 Order by T1.Code,T1.rownum

So, I thought I will make Sub query as separate view but that again throws error that variables not allowed in view. Please Help to overcome this situation.

Thanx in advance

Upvotes: 1

Views: 3554

Answers (1)

Andriy M
Andriy M

Reputation: 77657

You could try the method of triangle join + count for assigning row numbers. It will likely not perform well on large datasets, but instead you should be able to implement everything with a couple of views (if you think there's no other way to do what you want to do than with a view). The idea is as follows:

  1. The dataset is joined to itself on the condition of master.key >= secondary.key, where master is the instance where detail data will actually be pulled from, and secondary is the other instance of the same table used to provide the row numbers.

  2. Based on that condition, the first* master row would be joined with one secondary row, the second one with two, the third one with three and so on.

  3. At this point, you can group the result set by the master key column(s) as well as the columns that you need in the output (although in MySQL it would be enough to group by the master key only). Count the rows in every group will give you corresponding row numbers.

So, if there was a table like this:

CREATE TABLE SomeTable (
  ID int,
  Value int
);

the query to assign row numbers to the table could look like this:

SELECT m.ID, m.Value, COUNT(*) AS rownum
FROM SomeTable AS m
INNER JOIN SomeTable AS s ON m.ID >= s.ID
GROUP BY m.ID, m.Value
;

Since you appear to want to self-join the ranked rowset (and twice too), that would require using the above query as a derived table, and since you also want the entire thing to be a view (which doesn't allow subqueries in the FROM clause), you would probably need to define the ranking query as a separate view:

CREATE RankingView AS
SELECT m.ID, m.Value, COUNT(*) AS rownum
FROM SomeTable AS m
INNER JOIN SomeTable AS s ON m.ID >= s.ID
GROUP BY m.ID, m.Value
;

and subsequently refer to that view in the main query:

CREATE SomeOtherView AS
SELECT ...
FROM RankingView AS t1
LEFT JOIN RankingView AS t2 ON ...
...

This SQL Fiddle demo shows the method and its usage.

One note with regard to your particular situation. Your table probably needs row numbers to be assigned in partitions, i.e. every distinct Code row group needs its own row number set. That means that your ranking view should specify the joining condition as something like this:

ON m.Code = s.Code AND m.Month >= s.Month

Please note that months in this case are assumed to be unique per Code. If that is not the case, you may first need to create a view that groups the original dataset by Code, Month and rank that view instead of the original dataset.

* According to the order of key.

Upvotes: 1

Related Questions