MySQL grouping results by specific field

Question

I have a table named 'test' like so:

id     | location_id | date
1      | 1           | 2017-01-02
2      | 1           | 2017-01-03
3      | 2           | 2017-01-04
4      | 1           | 2017-01-05

I would like a SELECT statement that selects one row per location_id and ordered by date.

My specific problem is creating a statement that'll work with MySQL 5.7.1 and later, due to the ONLY_FULL_GROUP_BY requirements (and similar strict standards with other DBs)

SELECT id, location_id , date FROM test GROUP BY location_id  ORDER BY date

Works, but it's invalid the day the MySQL db is upgraded.

SELECT id, location_id , date FROM test GROUP BY location_id, id, date  ORDER BY date

Is valid, but does not work, it returns all the results.

What I would like is to obtain the results of the first SELECT query above, but with valid SQL that's future-proof, which returns:

id     | location_id | date
1      | 1           | 2017-01-02
3      | 2           | 2017-01-04

Any help or insight on how to get around this appreciated!

Answer 1

Based on the sample data you've provided you want to do this:

SELECT  MIN(id) AS ID,
        Location_ID,
        MIN(Date) AS Date
FROM    test
GROUP BY Location_ID
ORDER BY Date 

We've applied aggregate functions to id and date so you only need to keep location_id in the group by. and on the same data you seem to only choose the smallest id - date. This is probably a mislead assumption so let me know

EDIT

Based on our chat, I've come up with the following:

SELECT id, location_id, Date
FROM (
  SELECT t1.*,
         @cur := IF(location_id = @id, @cur+1, 1) AS RowNumber,
         @id := location_id AS IdCache
  FROM docs t1
  CROSS JOIN (
    SELECT @id:=(SELECT MIN(location_id) FROM docs), @cur:=0) AS init
   ORDER BY t1.location_id, date
) PartitionedData
WHERE RowNumber = 1
order by location_id

What I've done is replicate the methodology of MSSQL's ROW_NUMBER() and partitioned on location_id. To get a better understanding of that function read up on it here

Given the following example data:

1 | 1 | 2017-01-02
2 | 1 | 2017-01-03
3 | 2 | 2017-01-04
4 | 1 | 2017-01-05

The view, PartitionedData, will return the following data set:

id | location_id | date       | RowNumber | IdCache
1  | 1           | 2017-01-02 | 1         | 1
2  | 1           | 2017-01-03 | 2         | 1
4  | 1           | 2017-01-05 | 3         | 1
3  | 2           | 2017-01-04 | 1         | 2

The "Partitioning" is based on what we are choosing as the @id and then our order by. If we change the order by to be t1.location_id, date desc then our dataset would be:

id | location_id | date       | RowNumber | IdCache
4  | 1           | 2017-01-05 | 1         | 1
2  | 1           | 2017-01-03 | 2         | 1
1  | 1           | 2017-01-02 | 3         | 1
3  | 2           | 2017-01-04 | 1         | 2

So depending on how we order the date we can select the latest or first date - you'll have to handle the asc/desc in the php.

Finally, our returned data will coincide with the id so the above query will output:

id | location_id | date
1  | 1           | 2017-01-02
3  | 2           | 2017-01-04

Let me know if this is closer to what you are looking for!

Answer 2

Does SELECT DISTINCT give you what you need?

SELECT DISTINCT id, location_id , date FROM test GROUP BY location_id, id, date  ORDER BY date