MySQL SELECT first entry of group PER group PER day

Question

I have a table named worksheets_items similar to this set up

table "worksheet_items"
ID    DATE_ADDED    COUNTY_ID    NAME    COMPLETED
1     2016/03/15    11           "ZA"    0
2     2016/03/15    12           "FB"    1
3     2016/03/15    12           "GC"    0
4     2016/03/15    13           "GD"    1
5     2016/03/15    11           "EE"    1
6     2016/03/15    13           "TF"    1
7     2016/03/16    11           "YG"    0
8     2016/03/16    11           "WH"    0
9     2016/03/16    12           "YI"    1
10    2016/03/16    13           "QJ"    0
11    2016/03/16    13           "RK"    1
12    2016/03/16    12           "UL"    0
13    2016/03/16    11           "PM"    1

All I'm trying to do is get the one row (doesn't matter if it's first or not) per county per date. My goal is to display the date and every county under that date that has any entries that COMPLETED = 0

So in this case I would want to end up with

ID    DATE_ADDED    COUNTY_ID    NAME    COMPLETED
1     2016/3/15     11           "ZA"    0
3     2016/3/15     12           "GC"    0
7     2016/3/16     11           "YG"    0
12    2016/3/16     12           "UL"    0
10    2016/3/16     13           "QJ"    0

I tried using a GROUP BY but my expertise in MySQL is limited so it ended up just throwing errors out the wazoo. Any help at all would be greatly appreciated!

Answer 1

I think you don't need GROUP_BYat all

what you need is ordering records by date_added and county_id

SELECT * FROM worksheet_items
WHERE completed=0
ORDER BY date_added, county_id

• Sorting Function
• Group By Function

Answer 2

Get one ID per date and county (e.g. the maximum ID). Then access the records with the IDs thus found.

select *
from worksheet_items
where (date_added, county_id, id) in
(
  select date_added, county_id, max(id)
  from worksheet_items
  where completed = 0
  group by date_added, county_id
)
order by id;