sqlserver排序后，标号

table a

id   name
1     tom
2     Jery
3     Jack
4     Mary

select id,name ,返回排序后的位数 from a order by name desc
求返回排序后的
如 Mary 为第一位则返回 1
 
 
Sql代码 
select row_number() over (order by name desc) as rowNum,id,name from a order by name desc  
 相关
row_number() OVER (PARTITION BY COL1 ORDER BY COL2) 表示根据COL1分组，在分组内部根据 COL2排序，而此函数计算的值就表示每组内部排序后的顺序编号（组内连续的唯一的)
 
 
2011 9.7
Sql代码 
SELECT DBFile,Subject=CASE WHEN LEN(DBSubject)>22 THEN SUBSTRING(DBSubject,1,20)+'...' ELSE DBSubject END,SUBSTRING(DBDate,1,10) AS ICreateDate 
,TOPImg=case  
when row_number() over (ORDER BY DBSetTop asc,DBSort asc,DBDate desc)=1 then '<img src="images/top1.jpg" border="0" />'  
when row_number() over (ORDER BY DBSetTop asc,DBSort asc,DBDate desc)=2 then '<img src="images/top2.jpg" border="0" />'  
when row_number() over (ORDER BY DBSetTop asc,DBSort asc,DBDate desc)=3 then '<img src="images/top3.jpg" border="0" />'  
when row_number() over (ORDER BY DBSetTop asc,DBSort asc,DBDate desc)=4 then '<img src="images/top4.jpg" border="0" />'  
when row_number() over (ORDER BY DBSetTop asc,DBSort asc,DBDate desc)=5 then '<img src="images/top5.jpg" border="0" />'  
else '<img src="images/top6.jpg" border="0" />' end 
FROM tbInfo,tbCate,tbDatabase  
 WHERE DBID=IDBID AND ICate=CID AND  
 ICate LIKE '0110%' AND DBSend=1 ORDER BY DBSetTop asc,DBSort asc,DBDate desc 

作者“logout.jsp”