-1

Sorry, I have a question about relation between max value and column name.

My table is shown below:

===========================

basinID StationID Rainfall

LPS      C0R540   101.5

LPS      C0R600   13.5

LYS      01U050    23

LYS      01U080    3

===========================

I need to find max value on basinID and StationID also show.

the answer is as shown below:

===========================

basinID StationID Rainfall

LPS      C0R540   101.5

LYS      01U050    23

===========================

I have tried many methods but finally it is failure.

How should I do ??

Tony Lee
  • 3
  • 1
  • show your mysql query – Tamil Selvan C Jul 28 '15 at 03:07
  • possible duplicate of [How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?](http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql) – Strawberry Jul 28 '15 at 05:13

2 Answers2

0

Try my solution below:

SELECT * 
FROM (SElECT * FROM mytable ORDER BY Rainfall DESC) x
GROUP BY `Rainfall`

This works because in mysql you're allowed to not aggregate non-group-by columns, in which case mysql just returns the first row.

Thi Tran
  • 681
  • 5
  • 11
  • Yes, it's basically an an undocumented hack (unless you include the comments section of the relevant page of the documentation) – Strawberry Jul 28 '15 at 05:12
0

Maybe something like this?

SELECT t1.basinID, t1.StationID, t1.Rainfall
FROM mytable t1
LEFT JOIN mytable t2 ON t1.basinID = t2.basinID AND t1.Rainfall < t2.Rainfall
WHERE t1.basinID IS NULL;

The LEFT JOIN works on the basis that when t1.Rainfall is at its maximum value, there is no t2.Rainfall with a greater value and the t2 rows values will be NULL.

do_Ob
  • 709
  • 1
  • 6
  • 24