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
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
