Row_Number() Ranking Function Example

I recently had an opportunity to use the ROW_NUMBER() ranking function along with the OVER PARTITION BY clause. Here’s a simplified example of how I used it.

In this example the desired result is to get the first value in ColumnB for each group of Values in ColumnA.

ExampleTable:

Here’s the query:

SELECT   GroupNumber

,RankLetter

FROM (SELECT   ColumnA AS GroupNumber

                                ,ColumnB AS RankLetter

                                ,ROW_NUMBER() OVER(PARTITION BY ColumnA ORDER BY (SELECT 1)) AS Ranking

                FROM ExampleTable) AS TableAlias

WHERE Ranking = 1

Result:

There may have been another way to accomplish this, but I liked learning the ranking function. Hopefully this can help if you need to do something similar.