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.