Row Number Window Function | First Occurrence In A Series

Using the Row Number Window Function to flag records if that row is the first occurrence in a series is a performant way to extract more value from your data. By using a Partition By clause we can group chunks of data together while ordering them to figure out which is the first in that series of data. Row Number, with the addition of the Over clause, allows us to achieve this without the use of a subquery by simply wrapping the function in a case statement.

A meme of how we put the fun in window functions! — A meme of how we put the fun in row number window functions!

An example of how to find the first occurrence in a series


CASE WHEN(ROW_NUMBER() OVER(PARTITION BY U.ID ORDER BY P.CREATIONDATE)) = 1
     THEN 'Y'
     ELSE 'N'
END

CASE WHEN(ROW_NUMBER() OVER(PARTITION BY U.ID ORDER BY P.CREATIONDATE)) = 1

THEN 'Y'

ELSE 'N'

END

Let’s break this down: The power of the OVER clause allows us to “Group By” and “Order By” data without having to nest subqueries together. This also allows us to have many Window Functions in a single query while maintaining impressive performance. It’s this PARTITION BY clause that groups the data together, or partitions it, with a row number starting at 1 with each partition or grouping. So, the very first occurrence in series of data, as long as an order is defined, will always have a Row_Number of 1. We can take advantage of this by wrapping the window function in a case statement to mark a flag with Y or N if the row number returns 1 or not.

For my inquiring readers, you’re probably wondering how you could go about finding the last record in the series using this method. It’s clear that you won’t know what the last row number of a series will be, so how would you do that?

An example of how to find the last occurrence in a series


CASE WHEN(ROW_NUMBER() OVER(PARTITION BY U.ID ORDER BY P.CREATIONDATE DESC)) = 1
     THEN 'Y'
     ELSE 'N'
END

CASE WHEN(ROW_NUMBER() OVER(PARTITION BY U.ID ORDER BY P.CREATIONDATE DESC)) = 1

THEN 'Y'

ELSE 'N'

END

Notice that the only difference between these two functions is the use of defining the order. By default, the ORDER BY clause will sort ascending (ASC) but to find the last occurrence we simply swap the order to descending (DESC). It’s that easy!

The whole Row Number Window Function as an example:


SELECT
  U.DISPLAYNAME
 ,COALESCE(P.TITLE, 'Unknown') AS 'TITLE'
 ,CASE WHEN(ROW_NUMBER() OVER(PARTITION BY U.ID ORDER BY P.CREATIONDATE)) = 1
       THEN 'Y'
       ELSE 'N'
  END AS 'FIRST POST?'
FROM POSTS AS P
  INNER JOIN USERS AS U
    ON P.OWNERUSERID = U.ID;

SELECT

U.DISPLAYNAME

,COALESCE(P.TITLE, 'Unknown') AS 'TITLE'

,CASE WHEN(ROW_NUMBER() OVER(PARTITION BY U.ID ORDER BY P.CREATIONDATE)) = 1

THEN 'Y'

ELSE 'N'

END AS 'FIRST POST?'

FROM POSTS AS P

INNER JOIN USERS AS U

ON P.OWNERUSERID = U.ID;

This query returns the Name, Title, and a flag of Y or N delineating if it is that users first post or not. This query was built using Stack Overflow’s Data – click to see it in action!

It’s important to note that everything you see in this post is ANSI standard and will work in most, if not all RDBMs.