CODEWITHSUNDEEP
mysqlsql
Tom Leung
Tom Leung

Reputation: 374

How to select rows which have the biggest value of a column?

I don't know if my title is understandable or not, may be someone can help edit my title?

All I want to do is, for example:
I have a table like this

enter image description here


Engineering appears 5 times with different article_category_abbr, and I want to select only one row with the biggest value of num.
Here, it will be Engineering-ENG-192, and Geriatrics&Gerontology will be Geriatrics&Gerontology-CLM-26
But I don't know how to do it on the whole table using mysql

Upvotes: 0

Views: 42

Answers (3)

Andam
Andam

Reputation: 2167

USE MAX function and GROUP BY like this. Here is more information.

SELECT myID, classTitle, subField, MAX(score) FROM myTable GROUP BY myID, classTitle, subField

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

Join your table to a subquery which finds the greatest num value for each sc group.

SELECT t1.*
FROM yourTable t1
INNER JOIN
(
    SELECT sc, MAX(num) AS max_num
    FROM yourTable
    GROUP BY sc
) t2
    ON t1.sc = t2.sc AND
       t1.num = t2.max_num;

Upvotes: 1

John Woo
John Woo

Reputation: 263693

You can have a subquery that gets the largest value for each sc and the resulting rows will then be joined with the table itself based from two columns - sc and num.

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  sc, MAX(num) AS Num
            FROM    tableName
            GROUP   BY sc
        ) b ON a.sc = b.sc
            AND a.num = b.num

Here's a Demo

Upvotes: 1

Related Questions