Search content within the blog

Monday, May 25, 2009

Using RowNumber to obtain alternate rows based on column value

This was a strange requirement from the client ...she needed the output from the employee table as follows...

empname      gender
Emp1      M
Emp2      F
Emp3      M
Emp4      F

Thanks to subramanyam and vinod who solved this for me....

select name,gender, Row_number() over (order by gender) AS x from tblEmp Where Gender = 'M'
UNION
select name,gender, Row_number() over (order by gender) AS x from tblEmp Where Gender = 'F'
ORDER by name

=========Output=========
Ashwin      M
Payal      F
Vinod      M
Reena      F

No comments:

Post a Comment