GROUP BY a date, with ordering by date.

Question

Take this simple query:

SELECT DATE_FORMAT(someDate, '%y-%m-%d') as formattedDay
FROM someTable
GROUP BY formatterDay

This will select rows from a table with only 1 row per date.
How do I ensure that the row selected per date is the earliest for that date, without doing an ordered subquery in the FROM?

Cheers

Answer 1

I would rewrite your query like this:

SELECT distinct DATE_FORMAT(someDate, '%y-%m-%d') as formattedDay
  FROM someTable
 ORDER BY DATE_FORMAT(someDate, '%y-%m-%d') 

If you absolutely need group by (I.E. you are using an aggregate function you are not showing to us) just do:

SELECT DATE_FORMAT(someDate, '%y-%m-%d') as formattedDay
  FROM someTable
 GROUP BY DATE_FORMAT(someDate, '%y-%m-%d')
 ORDER BY DATE_FORMAT(someDate, '%y-%m-%d') -- this is redundant, but you can use another column here

Answer 2

If you are using MySQL there is no way to do what you want in a single select. You would need an inner query that selects the min ID and formatted or other field for each formatted date. Then you join it on the parent table again and select records with a minimum ID.

In other databases (postgresql, oracle, db2) there are windowing functions that allow to do this in a single select (i.e. DENSE_RANK() OVER ...).

Here is an example with an inner query (which you may already be aware of):

SELECT B.formattedDay, A.*
  FROM someTable A
       JOIN 
       (
           SELECT DATE_FORMAT(someDate, '%y-%m-%d') as formattedDay,
                  MIN(ID) as firstId
             FROM someTable I
         GROUP BY 1
       ) B ON (A.ID = B.firstId)

Change MIN(ID) for MIN(someDate) if needed and its a timestamp. If more than one record can appear with the same timestamp you will need to perform the operation twice. First getting minimum date per day, then minimum ID per minimum date.