CODEWITHSUNDEEP
mysqlsqlsql-order-bygroup-by
joxxe
joxxe

Reputation: 259

SQL - One record per date

Right now im having the following data:

        Name          Time

       picture  2012-07-23 17:00:00
       picture  2012-07-24 18:00:00
       picture  2012-07-24 19:00:00
       picture  2012-08-03 08:40:06
       picture  2012-08-03 08:42:39
       picture  2012-08-03 08:54:03
       picture  2012-08-03 10:38:58
       picture  2012-08-03 10:39:55
       picture  2012-08-06 08:12:14

And the following SQL query:

    SELECT DATE_FORMAT(DATE(DATE_SUB(time,INTERVAL 4 HOUR)),GET_FORMAT(DATE,'ISO'))
    as time, uri, media_id, description
    FROM media WHERE place_id = 1 
    GROUP BY time ORDER BY time DESC

Which works well and gives me the data i want. But the data from each unique date is ordered by the oldest date from each "day". I want it ordered by the newest. The Order By statement just order the whole result, not the dates for each day. If o just could take the newest date instead of the oldest when using Group By, but guess i have to work out something else than group by.

Edit: The following works. But i i want to return more columns from the subquery, how do I do that?

    SELECT Distinct DATE_FORMAT( DATE( DATE_SUB(time, INTERVAL 4 HOUR ) ) ,
    GET_FORMAT( DATE,  'ISO' ) ) AS date, 
            (SELECT type FROM media WHERE 
            DATE_FORMAT( DATE( DATE_SUB(time, INTERVAL 4 HOUR ) ) ,
           GET_FORMAT( DATE,'ISO' ) )  = date Order by time DESC Limit 1) as name
    FROM media

Upvotes: 5

Views: 6207

Answers (4)

GarethD
GarethD

Reputation: 69789

Use a subquery to return the maximum Time for each day:

SELECT  MAX(Time) AS Time
FROM    Media
WHERE   Place_ID = 1
GROUP BY CAST(Time AS DATE)

Then join this back to your media table to limit the results to one for each day:

SELECT  Type AS Name,
        DATE_FORMAT(DATE(DATE_SUB(Media.time, INTERVAL 4 HOUR)), GET_FORMAT(DATE, 'ISO')) AS Time,
        uri, 
        media_id, 
        description
FROM    Media
        INNER JOIN
        (   SELECT  MAX(Time) AS Time
            FROM    Media
            WHERE   Place_ID = 1
            GROUP BY CAST(Time AS DATE)
        ) MaxTime
            ON MaxTime.Time = Media.Time;

A massive benefit of this solution is that the principal applies accross all DBMS, whereas currently MySQL is the only one I am aware of that supports columns in the select statement that are not part of an aggregate function or contained in the group by statement. I have recently written another answer on SO describing the potential hazards of this functionality in MySQL, and why it is not supported in other DMBS yet.

Example on SQLFiddle

Upvotes: 5

Joe G Joseph
Joe G Joseph

Reputation: 24086

try this:

  SELECT time,DATE_FORMAT(DATE(DATE_SUB(time,INTERVAL 4 HOUR)),
         GET_FORMAT(DATE,'ISO')) as date, uri, media_id, description
  FROM media WHERE place_id = 1 
  ORDER BY date DESC ,time desc

Upvotes: 1

Serajul Rony
Serajul Rony

Reputation: 21

Change this "GROUP BY time ORDER BY time DES*C" to "*GROUP BY DATE ORDER BY time DATE". It will short the data from new date to old.

Upvotes: 2

ross_t
ross_t

Reputation: 511

Break your statement down slightly with substrings so that you have the ORDER BY on both the TIME and DATE seperate. Something like this should work;

SELECT DATE_FORMAT(DATE(DATE_SUB(time,INTERVAL 4 HOUR)),GET_FORMAT(DATE,'ISO')) 
as time, uri, media_id, description 
FROM media WHERE place_id = 1  
GROUP BY time ORDER BY SUBSTRING(time,1,10) DESC, SUBSTRING(time,12,8) ASC

Upvotes: 1

Related Questions