CODEWITHSUNDEEP
mysqlsqlmax
Nik
Nik

Reputation: 129

Find the maximum value(s) from a column and selecting their rows

After looking at other examples I still have not been able to find a solution, that is why I am asking for some help.

My table structure:

 V_id | name | group_id | other columns
 ----------------------
   1  |      |    1     
   2  |      |    1     
   3  |      |    2     
   4  |      |    3     
   5  |      |    3

I have been struggling to build a query, to select all the rows which have the maximum value from the group_id column.

therefore output should be like this:

V_id | name | group_id | other columns
----------------------
  4  |      |    3     
  5  |      |    3

which I believe can be solved by selecting all records where group_id is the highest.

and also need a query to get all the other remaining rows.
which in this case, should be like this:

V_id | name | group_id | other columns
----------------------
  1  |      |    1     
  2  |      |    1     
  3  |      |    2

which I believe can be done by selecting all records where group_id < Max(group_id)

Upvotes: 1

Views: 185

Answers (2)

Himanshu
Himanshu

Reputation: 32602

You can use JOIN for that:

SELECT a.* 
  FROM Table1 a
  JOIN (SELECT MAX(Group_ID) AS MAXID 
          FROM Table1) B
    ON a.Group_id = B.MaxID;

Result:

| V_ID |   NAME | GROUP_ID |
----------------------------
|    4 | (null) |        3 |
|    5 | (null) |        3 |

For the remaining rows use LEFT JOIN with a condition like this:

   SELECT a.* 
     FROM Table1 a
LEFT JOIN (SELECT MAX(Group_ID) AS MAXID 
             FROM Table1) B
       ON a.Group_id = B.MaxID
    WHERE B.MaxID IS NULL;

Result:

| V_ID |   NAME | GROUP_ID |
----------------------------
|    1 | (null) |        1 |
|    2 | (null) |        1 |
|    3 | (null) |        2 |

See this SQLFiddle

Upvotes: 1

John Woo
John Woo

Reputation: 263693

for the first part of the problem,

SELECT  *
FROM    tableName
WHERE   group_id = (SELECT MAX(group_ID) FROM TableName)

and for the second part,

SELECT  *
FROM    tableName
WHERE   group_id < (SELECT MAX(group_ID) FROM TableName)

Upvotes: 1

Related Questions