SQL With Ties 用法

原文链接：http://thomaszae.wordpress.com/2008/03/10/with-ties-on-sql-server-2005/

SQL Server 2005 has the functionality to select the top records but let’s say I want the same amount also will be loaded. For example, I’d like to select the top 5 currency rate records from a table, but if one of the records has the same value, don’t make it count as top 5. For illustrations you could see below example.

select top 5 * from batch where module = ‘CA’ order by curyrate desc

BatNbr CuryRate
000345 9900
000350 9900
000351 9900
000400 9800
000450 9750

If you look at that one, you will see that 9900 was loaded 3 times, let’s see if I run by below scripts.

select top 5 with ties * from batch where module = ‘CA’ order by CuryRate desc

BatNbr CuryRate
000345 9900
000350 9900
000351 9900
000400 9800
000450 9750
000451 9750
000475 9500
000456 9400

Could you see the difference?

 

下面的应用场景就可以考虑用with ties

StudentNo  Grade

1                 99

2                  100

3                  80

4                  99

5                  98

 

查出前三名的学生，结果集如下：

1    100

2    99

4    99

5    98