Query First 2 Records For Each Alphabet

Hi Friends,

Disclaimer first ;)
This question came on MSDN site few days back where one of our friend was seeking help to write a query which can retrieve first 2 records for each alphabet from a set of records. I thought of sharing it with you. But yes content/resolution is not mine I am just posting here for others help.

We have a table say named as "MSDN" with below structure & records in it.
















Results expected out of the query was like this:


So how to achieve this?

Thanks for our MSDN friend John to provide this query:

Select msdn.*
From msdn
Join
(
select empno, rank() over (partition by left(empname,1) order by empname) as r from msdn
) i on
msdn.empno=i.empno
and i.r<=2
order by empname

Original MSND post:
http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/9a0bee9c-855f-4591-b73b-82a71300b9d7