GROUP BY a date, with ordering by date

Take this simple query:

SELECT开发者_StackOverflow社区 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

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.

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