To Get the result table from table 'people' as below
+-----+--------+-------+ | row | gender | name | +-----+--------+-------+ | 1 | female | Lisa | | 2 | male | Greg | | 3 | female | Mary | | 4 | male | John | | 5 | female | Jenny | +-----+--------+-------+
Follow the below query
SELECT *
FROM (
SELECT people.*,
IF(gender=0, @mr:=@mr+1, @fr:=@fr+1) AS rank
FROM people, (SELECT @mr:=0, @fr:=0) initvars
) tmp
ORDER BY rank ASC, gender ASC;
Comments
Post a Comment