CODEWITHSUNDEEP
mysql
dcpartners
dcpartners

Reputation: 5456

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

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?

Upvotes: 1

Views: 95

Answers (4)

jbaylina
jbaylina

Reputation: 4708

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/

Upvotes: 0

Justin
Justin

Reputation: 9724

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 |

Upvotes: 1

John Woo
John Woo

Reputation: 263803

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

Upvotes: 3

NDM
NDM

Reputation: 6840

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

Upvotes: 0

Related Questions