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.