How to get ID where it's most hits grouping by date in mySQL?

Question

I have this table like this:

AdID | Hits | CreatedOn
1, 20, 2013-08-28 18:18:42
2, 12, 2013-08-28 13:34:42
3, 12, 2013-08-27 11:34:42
4, 14, 2013-08-27 02:34:42
5, 12, 2013-08-26 11:34:42
6, 12, 2013-08-26 02:34:42

I want to the result will be like this:

1, 20, 2013-08-28
4, 14, 2013-08-27
5, 12, 2013-08-26

Basically it will select the most hist of each date and if it's the HITS same it will pick top of AdID

How do I do this in mySQL?

you need to group by hits then group by date order by most recent date — MaveRick, Aug 22 '13 at 07:48
See also: [How to select `date` from `datetime` column?](http://stackoverflow.com/q/1754411/1935077) — Petr R., Aug 22 '13 at 07:49

John Woo · Answer 1 · 2013-08-22T07:50:43.340

3

This will perform a little slower since you need to extract time from date but will give you the result you need.

SELECT  a.AdID, a.Hits, DATE(a.CreatedON) CreatedON
FROM        tableName a
        INNER JOIN
        (
            SELECT  DATE(CreatedON) CreatedON,
                    MAX(Hits) Hits 
            FROM    tableName
            GROUP   BY DATE(CreatedON)
        ) b ON a.Hits = b.Hits AND  
                DATE(a.CreatedON) = b.CreatedON

SQLFiddle Demo

edited Aug 22 '13 at 07:50

answered Aug 22 '13 at 07:45

John Woo

258,903
69
498
492

This will give two rows for a date if they have the same number of hits. – jbaylina Aug 22 '13 at 08:17
@jbaylina exactly. they have the same number and the most hit of the day. – John Woo Aug 22 '13 at 08:19
The question sais to take only the one with lower AdID. – jbaylina Aug 22 '13 at 08:24

Justin · Answer 2 · 2013-08-22T08:32:24.327

Query:

SQLFIDDLEExample

SELECT  a.AdID, a.Hits, DATE(a.CreatedOn) CreatedON
FROM tableName a
WHERE a.AdID = (SELECT b.adID
                FROM tableName b
                WHERE DATE(b.CreatedOn) = DATE(a.CreatedOn)
                ORDER BY b.Hits DESC, b.adID ASC
                LIMIT 1)

Result:

| ADID | HITS |                     CREATEDON |
-----------------------------------------------
|    1 |   20 | August, 28 2013 00:00:00+0000 |
|    4 |   14 | August, 27 2013 00:00:00+0000 |
|    5 |   12 | August, 26 2013 00:00:00+0000 |

score 0 · Answer 3 · answered Aug 22 '13 at 07:45

0

If I understand correctly, you want to GROUP BY hits and, then pick the lowest or highest ID, so you will need 2 things: a group and ordering:

SELECT AdID FROM table GROUP BY Hits ORDER BY AdID ASC LIMIT 1

This will give you the lowest AdID for the group with the most Hits. If you want the highest AdID, you need to switch ASC with DESC ofcource

answered Aug 22 '13 at 07:45

NDM

6,731
3
39
52

but this will only return 1 row!! – MaveRick Aug 22 '13 at 07:46
Need grouping by date NOT hits ... then it picks the MAX of that hits and the id of that MAX hits. – dcpartners Aug 22 '13 at 07:46
If you need grouping by date, this will not work; it is impossible in 1 query, without subqueries. – NDM Aug 22 '13 at 08:05

jbaylina · Answer 4 · 2013-08-22T08:36:34.130

SELECT *
FROM TableName T1
WHERE ( SELECT count(*)
        FROM TableName T2
        WHERE      (T2.Hits < T1.Hits OR (T2.Hits=T1.Hits AND T2.AdID < T1.AdID)) 
               AND  DATE(T2.CreatedOn)=DATE(T1.CreatedOn)
      ) < 1

This way you can select the 2nd, 3rd, the top 3 for each date, etc.

Check this interesting link: http://www.xaprb.com/blog/2008/08/08/how-to-select-the-nth-greatestleastfirstlast-row-in-sql/

How to get ID where it's most hits grouping by date in mySQL?

4 Answers4