SQL Queries Joes 2 Pros: SQL Query Techniques For Microsoft SQL Server 2008, Volume 2 by Morelan Rick A.;Ekberg Tom;Berger Irina;Jessica Brown;Brown Jessica

Author:Morelan, Rick A.;Ekberg, Tom;Berger, Irina;Jessica Brown;Brown, Jessica [Morelan, Rick A.;Ekberg, Tom;Berger, Irina;Jessica Brown;Brown, Jessica]
Language: eng
Format: mobi
Publisher: Book Surge
Published: 2010-01-21T05:00:00+00:00

Add RANK( ) to the SELECT list. Remember that all ranking functions need an OVER( ) clause. If you forget the OVER( ), SQL Server reminds you (Fig 7.3).

SQL Server provides another syntax clue for the ORDER BY clause (Figure 7.4).

Ranking functions need the ORDER BY information to appear as an argument of the OVER( ) clause (see Figures 7.4, 7.5).

Observe that no ORDER BY clause follows the FROM clause, since we moved it in into the OVER( ) clause (see Figure 7.6).

Our grant data is in order, nicely shows the ranking, and we’ve named our rank column GrantRank (see Figure 7.6). Examine what the RANK( ) function has done here. We see straightforward rankings of 1, 2, and 3 for the first three unique amount values. Notice the tie for fourth place. Then there’s another tie for the next place, which is ranked as 6.