Create a view to add a row no. for a specific set of data filtered by a col

[chandimak]

I have a database table like the one mentioned below.

id    | lecture            | subject_id | date       | is_deleted
------|--------------------|------------|------------|-----------
 1    | Introduction       | 1          | 2012-08-10 | 0   
 2    | Structure          | 2          | 2012-08-15 | 1   
 3    | Introduction       | 2          | 2012-08-12 | 0   
 4    | Functions          | 1          | 2012-08-14 | 1   
 5    | Material           | 2          | 2012-08-18 | 0   
 6    | Requirements       | 1          | 2012-08-16 | 0   
 7    | Analysis           | 1          | 2012-08-11 | 0

I need to make a view out of this table, which will display a row no. (flow no.) for each subject ordered by date, removing is_deleted = 1 rows. Simply, making a flow no. for each lecture in a particular subject ordered by date only with not deleted lectures. So, the view made by above data will look like the following.

flow_no | id   | date       | lecture            | subject_id 
--------|------|------------|--------------------|------------
 1      | 1    | 2012-08-10 | Introduction       | 1          
 2      | 7    | 2012-08-11 | Analysis           | 1          
 3      | 6    | 2012-08-16 | Requirements       | 1          
 1      | 3    | 2012-08-12 | Introduction       | 2          
 2      | 5    | 2012-08-18 | Material           | 2  

I tried to do this in several ways and everything failed. It's highly appreciated if someone could help me to resolve this.

[smantscheff]

select id, lecture, Table1.subject_id, date, c,
(select count(subject_id) from Table1 as t 
 where t.subject_id = Table1.subject_id
 and t.date <= Table1.date
 and is_deleted != 1
) as flow_id
from Table1
inner join
(
   select subject_id, count(subject_id) as c
   from Table1
   where is_deleted != 1
   group by subject_id
) as counts
on Table1.subject_id = counts.subject_id
where  is_deleted != 1
order by subject_id, date;

See http://sqlfiddle.com/#!3/c689b/7/0

[chandimak]

Thank you very much smantscheff. I didn't think this problem could be solved this way. It's really helpful for me. By the way it gives same flow_id if the date is same. It's highly appreciated if you could support to solve this issue.

Changing last row in the schema to the following will generate this issue. Thanks in advance.

('7', 'Analysis', '1', '2012-08-10', '0');

[smantscheff]

It depends on the exact semantics of your data. If the flow order is also the order of the IDs, you can do it as shown below. Otherwise you need additional criteria do determine the flow order.

select id, lecture, Table1.subject_id, date, c,
(select count(subject_id) + 1 from Table1 as t 
 where t.subject_id = Table1.subject_id
 and (t.date < Table1.date or (t.date = Table1.date and t.id < Table1.id))
 and is_deleted != 1
) as flow_id
from Table1
inner join
(
   select subject_id, count(subject_id) as c
   from Table1
   where is_deleted != 1
   group by subject_id
) as counts
on Table1.subject_id = counts.subject_id
where  is_deleted != 1
order by subject_id, date;

[chandimak]

Thank you very much again smantscheff for your detailed, complete answer and for your time. It does the trick. Now it exactly works as I wished.