SQL sort by count with condition, show history

Question

Consider the following simple MySQL query:

SELECT date, count(*) cnt, ip 
FROM fwlog
GROUP BY ip, date
HAVING cnt>100
ORDER BY date DESC, cnt DESC

It gives me something like:

date    cnt     src
2013-06-20      14441   172.16.a
2013-06-20      8887    172.16.b
....
2013-06-19      14606   172.16.b
2013-06-19      12455   172.16.a
2013-06-19      5205    172.16.c

That is, it's sorting the IPs by date, then by count, as directed.

Now I would like the result to be:

• Show the IP with the highest count TODAY first,
• then show the counts for that IP for the last few days (independent of cnt),
• then show the IP with the second hightest count TODAY,
• then the history of that IP,
• etc.pp.

Example of desired result:

date    cnt     src
2013-06-20      14441   172.16.a
2013-06-19      12455   172.16.a
2013-06-18      ....    172.16.a
2013-06-17      ....    172.16.a
....
2013-06-20      8887    172.16.b
2013-06-19      14606   172.16.b
2013-06-18      ....    172.16.b
2013-06-17      ....    172.16.b
...
2013-06-20      ....    172.16.c
2013-06-19      ....    172.16.c
2013-06-18      ....    172.16.c
2013-06-17      ....    172.16.c
...
...

Can this even be done using plain SQL?

Bye,

Marki

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

@ Gordon Linoff:

SELECT 
    datex,
    cnt,
    ip
FROM
    fwlog
WHERE
    ...
GROUP BY ip , datex
ORDER BY SUM(case
    when datex = DATE(NOW()) then 1
    else 0
end) DESC , src, date DESC, cnt DESC

2013-06-20      47      10.11.y
2013-06-19      47      10.11.y
2013-06-18      45      10.11.y
2013-06-17      42      10.11.y
2013-06-16      14      10.11.y
....
2013-06-20      592     172.16.a
2013-06-19      910     172.16.a
2013-06-18      594     172.16.a
2013-06-17      586     172.16.a
2013-06-20      299     172.16.b

This is not quite right yet, the lower block should be at the top.

Answer 1

Try:

SELECT a.`date`, count(*) cnt, a.ip 
FROM fwlog a
JOIN (SELECT ip, count(*) today_count
      FROM fwlog 
      where `date` = date(now()) 
      group by ip) t
  ON a.ip = t.ip and t.today_count > 100
GROUP BY a.ip, a.date
ORDER BY t.today_count DESC, a.ip, a.`date` DESC

Answer 2

The following seems to work. Thanks.

SELECT 
    str_to_date(a.date, _utf8'%e%b%Y') datex,
    count(*) cnt,
    a.src
FROM
    fwlog a
        JOIN
    (SELECT 
        src, count(*) latest_count
    FROM
        fwlog
    WHERE
        str_to_date(fwlog.date, '%e%b%Y') = subdate(current_date, 1)
            AND ((fwlog.action <> 'accept')
            AND (fwlog.message_info <> 'Implied rule')
            AND (fwlog.proto NOT IN ('icmp' , 'igmp')))
    GROUP BY src) b ON a.src = b.src AND b.latest_count > 10
WHERE
    ((a.action <> 'accept')
        AND (a.message_info <> 'Implied rule')
        AND (a.proto NOT IN ('icmp' , 'igmp')))
GROUP BY a.src , datex
ORDER BY b.latest_count DESC , a.src , datex DESC

• str_to_date is used because the format is initially no real date type
• there are a few WHERE conditions, you have to take care they are present in the inner and outer query
• subdate is used because the latest_count is from yesterday (but that's a detail, it will work with today too)

In the end you get a nice result like this:

'2013-06-20', '14441', '172.16.a'
'2013-06-19', '14606', '172.16.a'
'2013-06-18', '14338', '172.16.a'
'2013-06-17', '7665', '172.16.a'
'2013-06-20', '8887', '172.16.b'
'2013-06-19', '12455', '172.16.b'
'2013-06-18', '10040', '172.16.b'
'2013-06-20', '5744', '172.16.c'
....

I am accepting Mark Bannister's answer because it is closest to what works.

Answer 3

You need to add a count for the current date into your order by clause. Getting the current date differs among databases. Here is the version for SQL Server:

SELECT date, count(*) cnt, ip 
FROM fwlog
GROUP BY ip, date
HAVING cnt > 100
ORDER BY SUM(case when date = cast(GETDATE() as date) then 1 else 0 end) desc,
         ip,
         date DESC, cnt DESC

The sort on ip takes care of the case when two have the same current count.

Other databases might use now(), sysdate()`, CURRENT_TIMESTAMP, or something like that or the current date.