SQL - One record per date

Question

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

The problem is that Group By is not a sorting function. So it makes no differences. I think i need a subquery. — joxxe, Aug 07 '12 at 06:17
my bad. The MySQL docs seem to imply that you can do that. i.e., on this page: http://dev.mysql.com/doc/refman/5.0/en/select.html ... so if it doesn't do that, I'd be interested in what the ASC|DESC is for when used with group by — imm, Aug 07 '12 at 06:21
If i do SELECT Distinct DATE_FORMAT(DATE(DATE_SUB(time,INTERVAL 4 HOUR)),GET_FORMAT(DATE,'ISO')) as date FROM media WHERE place_id = 1 Then i get all the dates. Maybe just add a subquery to that query that gets the info from the most recent for for every date. — joxxe, Aug 07 '12 at 06:22

score 5 · Accepted Answer · edited May 23 '17 at 12:07

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

Whoa! Thank you! It works very well. I´ve got an own solution, that did a subquery for each column. It worked but wasnt that efficient. Your solution is much better :) — joxxe, Aug 07 '12 at 07:35

score 2 · Answer 2 · answered Aug 07 '12 at 06:11

2

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.

answered Aug 07 '12 at 06:11

Serajul Rony

21
5

score 1 · Answer 3 · answered Aug 07 '12 at 06:10

1

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

answered Aug 07 '12 at 06:10

ross_t

511
5
10

I'm not clear what distinctive columns you have on your table, but if DATE and TIME are seperate values then why can't you just try adjusting your statement to do 'ORDER BY DATE DESC, TIME ASC' – ross_t Aug 07 '12 at 06:27
Did you try the SUBSTRING that I suggested on your original query? – ross_t Aug 07 '12 at 06:45
This groups by time, so does not return a unique result for each date, it returns all items sorted by date. [As shown on SQLFiddle](http://sqlfiddle.com/#!2/f7225/12). I assume you are trying to reference your column in the select list with the alias `time` in the group by, if so you need to give it a different alias to avoid ambiguity [e.g. Time2](http://sqlfiddle.com/#!2/f7225/13) – GarethD Aug 07 '12 at 07:38

score 1 · Answer 4 · answered Aug 07 '12 at 06:19

1

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

answered Aug 07 '12 at 06:19

Joe G Joseph

23,518
5
56
58

SQL - One record per date

4 Answers4