how to get rank according to group and year of birth

Question

I have a table with gr_no, year_dob, family_id etc. etc. I am trying to rank birth year according to family_id and am unable to generate the siblings_rank result.

+----------+--------------+-----------+
| gr_no    | year_dob     | family_id | siblings_rank
+----------+--------------+-----------+
|     1001 | 1992         |    95     |  1
|    10234 | 1995         |    95     |  2
|    10236 | 2004         |    96     |  1
|    15568 | 2006         |    96     |  2
|     1225 | 2004         |    92     |  1
+----------+--------------+-----------+

This query is working :

SET @prev := null;

SET @cnt := 1;

SELECT gr_no, gs_id, gf_id, year_dob, IF(@prev <> gf_id, @cnt := 1, @cnt := @cnt + 1) AS siblings_position, @prev := gf_id as previous_gf_id
FROM student_registered
ORDER BY gf_id, year_dob asc

This query is also working:

SELECT gr_no, gs_id, gf_id, year_dob, IF(@prev <> gf_id, @cnt := 1, @cnt := @cnt + 1) AS siblings_position, @prev := gf_id as previous_gf_id
FROM student_registered
JOIN (SELECT @prev := null) p
JOIN (SELECT @cnt := 1) c
ORDER BY gf_id, year_dob asc

............... I am unable to create view with these query?

or If a procedure can update the student_registered column 'siblings_position' based on the queries?

Answer 1

you can't use sql variables in the view

here is another way to get the same result using correlated subquery

SELECT gr_no, family_id,year_dob,
       ( select count(*) from Table1  T1
         where T1.family_id = T.family_id
         and T1.year_dob <= T.year_dob) as siblings_position
FROM Table1 T
ORDER BY family_id, year_dob asc