Methods For Selecting Rows With The Minimum Value For Their Group in SQL

Methods For Selecting Rows With The Minimum Value For Their Group in SQL

In this tutorial we are going to show you how we can select Rows with the minimum value for their respective groups in SQL. There are 5 ways mentioned below that lets you understand the the methods to get through it without running into any error. We can sort the order by using “Group By” clause with “MIN” function. Follow the examples shown below to comprehend the methods.

SELECT 
    Contestant,
    MIN( Score ) AS MinScore
FROM Gameshow
GROUP BY Contestant
ORDER BY Contestant;

Besides, it’s also simple to to use correlated subquery to sort this further.

SELECT 
    Contestant,
    Game,
    Score
FROM Gameshow g1
WHERE Score = ( SELECT MIN( g2.Score )
              FROM Gameshow g2
              WHERE g1.Contestant = g2.Contestant )
ORDER BY Contestant;

This may be not an efficient way because we referring to columns which are outside of the subquery. These queries are executed repeatedly, once for each row they are selecting. That is why they are called repeating subqueries.
We can also use uncorrelated subquery.

This may not turn out useful as you can see what we meant by saying that is about the columns that are outside the subcategory. So the queries will be executed repeatedly for every row that they have selected. That’s how they got their name as well “repeating subqueries.”

However, for that we may use uncorrelated subquery, as they don’t need outer query in order to execute them.

SELECT 
    g1.Contestant, 
    g1.Game,
    g1.Score
FROM Gameshow g1
JOIN (
  SELECT Contestant, MIN( Score ) AS Score
  FROM Gameshow
  GROUP BY Contestant ) AS g2
  ON g1.Contestant = g2.Contestant AND g1.Score = g2.Score
ORDER BY Contestant ASC;

In oracle, we do not use “AS” when declaring column ‘aliases’.

SELECT 
    g1.Contestant, 
    g1.Game,
    g1.Score
FROM Gameshow g1
JOIN (
  SELECT Contestant, MIN( Score ) Score
  FROM Gameshow
  GROUP BY Contestant ) g2
  ON g1.Contestant = g2.Contestant AND g1.Score = g2.Score
ORDER BY Contestant ASC;

Instead we can use “LEFT JOIN” that is shown in the example below to explain it further more openly.

SELECT 
    g1.Contestant, 
    g1.Game,
    g1.Score
FROM Gameshow g1
LEFT JOIN Gameshow g2 ON 
    g1.Contestant = g2.Contestant AND g1.Score > g2.Score
WHERE g2.Contestant IS NULL
ORDER BY g1.Contestant ASC;

However, we can also make use of common table expression here.

WITH cte AS (
   SELECT Contestant, Game, Score,
            RANK() OVER ( PARTITION BY Contestant
            ORDER BY Score ASC
            ) AS r
    FROM Gameshow
)
SELECT Contestant, Game, Score
FROM cte
WHERE r = 1
ORDER BY Contestant ASC;

So this is how we can select rows in SQL for their group with the minimum value in them. You can easily repeat for this technique over for different datasets. You can also replace the data so that you can easily remember this. For more queries leave the comments below.