CODEWITHSUNDEEP
mysqlsqljoinsubquerymin
sora0419
sora0419

Reputation: 2378

Get Lowest Value While Comparing a Different Column

I'm trying compare the data in a table and output the lowest value of each type. I know there's a MIN() function in SQL, but it will only get the lowest value overall, which is not exactly I want.

I want to get the data of the whole row, whose value in Column1 is the smallest foreach type.

This is how the table looks like:

Type        Column1         Column2
-------     --------        ---------
A           1               !!!
A           2               @@@
B           3               ###
B           4               $$$
C           5               %%%
C           6               ^^^

When I use the SELECT MIN(Value), the result I got looks like this:

Type        Column1         Column2
-------     --------        ---------
A           1               !!!

I used the GROUP BY method suggest by Linger, but the result still didn't quite match what I want:

Type        Column1         Column2
-------     --------        ---------
A           1               @@@
B           3               $$$
C           5               ^^^

However, the desire result is suppose to be:

Type        Column1         Column2
-------     --------        ---------
A           1               !!!
B           3               ###
C           5               %%%

How would I write my SQL query to do the job? Thanks in advance.

Upvotes: 0

Views: 394

Answers (1)

Linger
Linger

Reputation: 15048

That is a simple query to perform simply by using the GROUP BY clause:

SELECT MTM.* FROM 
(
    SELECT MTS.Type, MIN(MTS.Values) AS SmallestValue
    FROM  MyTable AS MTS
    GROUP BY MTS.Type
) AS MainQ
INNER JOIN MyTable AS MTM 
ON MTM.UniqueID = MainQ.UniqueID

In the above example I am performing the MIN() GROUP BY query and then joining the results to the same table to pull the remaining fields I am in need of. You should have a field that uniquely identifies a record. Use that field instead of UniqueID above.

Upvotes: 1

Related Questions