How to solve this query in mysql


Problem :

I have this records in MySQL below

RecID | Description | Date | Hits | IsPublished 
1; "Test"; 04/10/2012; 45; True
2; "Test 1"; 04/10/2012; 37; True
3; "Test 2"; 05/10/2012; 12; True
4; "Test 3"; 05/10/2012; 13; True
5; "Test 4"; 07/10/2012; 14; True
6; "Test 5"; 07/10/2012; 25; True
7; "Test 4"; 08/10/2012; 23; True
8; "Test 5"; 08/10/2012; 35; True
9; "Test 9"; 12/10/2012; 7; True

Would like to achieve the following

9; "Test 9"; 12/10/2012; 7; True 
8; "Test 5"; 08/10/2012; 35; True
4; "Test 3"; 05/10/2012; 13; True

Basically, the first rule is to group the date which is 08/10/2012, 07/10/2012 and 05/10/2012 and 04/10/2012. Use this 08/10/2012 as a starting point. Then remove any dates that is close to each other (min 1 day).

BTW ... this is an Australian date (DD/MM/YYYY). Therefore, the result is 08/10/2012, 05/10/2012. Out of these 2 "valid" days and then pick the record that has more hits on that day. Then set the others as IsPublished = false.

Any ideas to do this in MySQL?



Solution :

First find the dates that have no other rows with a day larger by 1:

    SELECT DISTINCT t1.Date
    FROM table_name t1
    LEFT JOIN table_name t2
      ON DATE_ADD(t1.Date, INTERVAL 1 DAY) = t2.Date
    WHERE t2.RecId IS NULL

Then find the max hits for these dates:

  SELECT Date, MAX(Hits) as maxHits
  FROM table_name
  WHERE Date IN (
    SELECT DISTINCT t1.Date
    FROM table_name t1
    LEFT JOIN table_name t2
      ON DATE_ADD(t1.Date, INTERVAL 1 DAY) = t2.Date
    WHERE t2.RecId IS NULL )
  GROUP BY Date

Finally, update all rows that don't match these dates and maxHits:

UPDATE table_name toUpdate, (    
  SELECT Date, MAX(Hits) as maxHits
  FROM table_name
  WHERE Date IN (
    SELECT DISTINCT t1.Date
    FROM table_name t1
    LEFT JOIN table_name t2
      ON DATE_ADD(t1.Date, INTERVAL 1 DAY) = t2.Date
    WHERE t2.RecId IS NULL )
  GROUP BY Date) source
SET toUpdate.IsPublished = false
WHERE toUpdate.Date != source.Date OR toUpdate.Hits != source.maxHits

Mysql Tutorials

Mysql Howto..

How to get rid of STRICT SQL mode in MySQL

How to use 2 condition in mysql query? [closed]

How to add data off HTML page into MySQL DB

How to import yml file into MySQL Database?

Mysql how to set time data type to be only HH:MM in database

How to update Sphinx Realtime Index with field = old_value + new_value?

How can I access a PDO connection from a different file?

How to connect these 3 programming languages?

How I can derive count(*) from another table using LEFT JOIN mysql

Canonical: How to save HTML form data into MySQL database

How to query in Mysql Index

how to upload excel data's into mysql table

Simple password encryption - how do i do? [closed]

How does one craft an array from LEFT JOINs when joined tables have multiple matches and GROUP BY omits needed rows

how to create a search form in php and mysql with pagination?

Django Admin Won't Show Fields

How to disable and enable all constraints in table mysql incl. PK FK CHK UNI etc

How to drop composite UNIQUE KEY in MySQL?

Outputting all data in a table and showing which rows belong to a user

How to insert point data into mysql using PDO bindParam?

How to sum a generated column in MySQL?

How to/ Where to delete/edit an index from MySql which prevents duplicates row

how to use MySQL and MSSQL both in ASp.net application with Entity framework

How to retrieve ID from other table for composite key in another table in mysql

How to select the entire tuple that has the MAX value from a subquery?

How To SUM, GROUP and SPLIT data in MYSQL Database Based on Dates

how to con-cat dynamic ID with select query in mysql

how to pass dropdown dynamic value in php/mysql

how to generate link from mysql query results in php

Trouble understanding how to get rid of a table scan in a basic MYSQL explain example