MySQL: Select rows with max column value and corresponded datetime grouped by another column

Question

My table is

IP  HITS    DATETIME
ip1 90      2016-09-10 01:15:37
ip2 13      2016-09-10 04:16:41
ip3 14      2016-09-10 05:17:41
ip2 12      2016-09-10 07:18:42
ip3 45      2016-09-10 09:19:42
ip1 88      2016-09-10 13:20:43
ip2 15      2016-09-10 15:21:43
ip3 26      2016-09-10 18:22:44

the result is

IP  HITS    DATETIME
ip1 90      2016-09-10 01:15:37
ip2 15      2016-09-10 15:21:43
ip3 45      2016-09-10 09:19:42

Any suggestions would be greatly appreciated.

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Sep 12 '16 at 15:32
Duplicate: http://stackoverflow.com/questions/7594465/group-wise-maximum-of-a-certain-column and http://stackoverflow.com/questions/15211479/groupwise-maximum — R. Chappell, Sep 12 '16 at 15:35

Pரதீப் · Accepted Answer · 2016-09-12T15:42:35.660

1

Here is one way using Correlated sub-query

select * from yourtable a
where hits = (select max(hits) from yourtable b where a.IP = b.IP)

Another way using JOIN

SELECT a.IP,a.HITS,a.DATETIME
FROM   yourtable a 
       JOIN (SELECT Max(hits) AS max_hits, 
                    ip 
             FROM   yourtable 
             GROUP  BY ip) b 
         ON a.ip = b.ip 
            AND b.max_hits = a.hits

edited Sep 12 '16 at 15:42

answered Sep 12 '16 at 15:32

Pரதீப்

91,748
19
131
172

MySQL: Select rows with max column value and corresponded datetime grouped by another column

1 Answers1