TIL... About SQL Row Number
Recently, I found myself needing to add a new column to a view. I needed the new column to link to the latest entry in another table for every entry in the first. Thankfully the table I needed the latest from included a DateModified column. So far so good. But I needed some function that would allow me to pick the latest from the series.
This is when a mentor suggested I use the ROM_NUMBER function in sql. This handy function will produce the ordinal ‘row number’ for a sequence of rows partitioned by a given values and then ordered by a second. It looks a little like this:
The output of this query is a table with 3 columns: id, linkedId, row. The results might look like:
This makes it very simple to pick the ‘latest’ because it is implicitly the first row, sop when I encorporated that query into the view it looked a little like this:
INNER JOIN (
I’d never heard of ROW_NUMBER until this time, but I found it very useful, and will be adding it to my toolkit going forward. I hope this explanation will help you do likewise!