select ROW_NUMBER() OVER (PARTITION BY guide_id ORDER BY pic_sort) as rowid,pic_id,guide_id from [D_Guides_PIC]
ROW_NUMBER()从1开始，为每一条分组记录返回一个数字，这里的 ROW_NUMBER() OVER (PARTITION BY guide_id ORDER BY pic_sort) 是先把pic_sort列升序，再为升序以后的pic_sort记录返回一个序号。
select a.id,a.title,b.img_url from [table1] as a left join (select ROW_NUMBER() OVER (PARTITION BY id ORDER BY pic_sort) as rowid,* from [table2]) as b on a.id=b.g_id and b.rowid=1 --排序分组取组内第一条
原文：http://www.cnblogs.com/hedgerow/p/4099400.html