sentient beings

Many queries you write will be about finding the most recent data for a certain type of event, or the highest value for a certain object. While this is a straightforward query type, it gets complicated and ugly when you have to link two or more tables to find your maximum or minimum value. And it gets even more complicated when you don’t have to find the maximum value but the value that is just below the maximum value.

In the rest of the article, I will be talking about the maximum value for a field, but it’s also true of course for the minimum value.
The query type you will encounter most often contains a subquery where the current value is compared to the maximum value for that particular field. A simple example is presented below.

 
SELECT
  MAX(tb2.Field) AS MaxValueForField
FROM
  table1 tb1
    INNER join
  table2 tb2
    ON  tb2.foreign_key = tb1.primary_key
WHERE
  tb2.Field = (
          SELECT
            MAX(_tb2.field)
          FROM
            table1 _tb1
              INNER join
            table2 _tb2
              ON  _tb2.foreign_key = _tb1.primary_key
          WHERE
            _tb1.primary_key = tb1.primary_key
        )
GROUP BY
  tb1.primary_key
 

The subquery can be avoided with a Common Table Expression that not only speeds up the entire query but also offers a solution for another problem that I will be discussing below.

 
;
WITH tb2Sorted
 
AS
 
(
  SELECT
    Row_Number() OVER (partition BY tb1.primary_key ORDER BY tb2.Field DESC) AS RowId,
    Field
  FROM
    table1 tb1
      INNER join
    table2 tb2
      ON  tb2.foreign_key = tb1.primary_key
)
SELECT
  Field AS MaximaleIngangsDatum
FROM
  tb2Sorted
WHERE
  RowId = 1
 

This query performs better than the first one but also offers another advantage.

You can easily find the second or third largest value by simple changing the RowId. Bear in mind that this will not remove duplicates. If the values for Field are 1,1,2,5,5 then RowId 1 will yield 5 and RowId 2 will also yield 5. You can find the top 5 largest values for a certain field by simply changing the where clause to read RowId < 6.

 
;
WITH tb2Sorted
 
AS
 
(
  SELECT
    Row_Number() OVER (partition BY tb1.primary_key ORDER BY tb2.Field DESC) AS RowId,
    Field,
    RowId
  FROM
    table1 tb1
      INNER join
    table2 tb2
      ON  tb2.foreign_key = tb1.primary_key
)
SELECT
  Field AS MaximaleIngangsDatum
FROM
  tb2Sorted
WHERE
  RowId < 6