Select row with most recent date

Question

Sample table data:

+---------+--------+-------+---------------------+----------+
|   ref   |  cost  | stock |        date         | location |
+---------+--------+-------+---------------------+----------+
| 00AD075 | 352.33 |     6 | 2017-06-14 08:24:43 | WA       |
| 00AD075 | 352.33 |     4 | 2017-06-19 06:01:14 | WA       |
| 00AD075 | 352.33 |     0 | 2017-06-22 00:00:00 | WA       |
| 00AE912 | 231.46 |     2 | 2017-06-14 08:24:43 | WA       |
| 00AE912 | 231.46 |     1 | 2017-06-15 06:31:03 | WA       |
| 00AE912 | 231.46 |     2 | 2017-06-16 06:01:16 | NY       |
| 00AE912 | 231.46 |     0 | 2017-06-19 00:00:00 | WA       |
| 00AE938 | 852.25 |     2 | 2017-06-14 08:24:43 | NY       |
| 00AG510 | 172.27 |     1 | 2017-06-14 08:24:43 | NY       |
| 00AG520 | 359.67 |     6 | 2017-06-14 08:24:43 | NY       |
| 00AG590 | 747.21 |     1 | 2017-06-14 08:24:43 | CA       |
| 00AG590 | 550.00 |     1 | 2017-06-15 06:31:03 | CA       |
+---------+--------+-------+---------------------+----------+

pseduo:

• fetch all where ref LIKE '00%' AND stock > 0
• order by date DESC
• Keep the most recent and discard all other rows with same location
• if stock not greater than 0 discard it too

Expected data:

+----------+----------+---------+-----------------------+-----+
| 00AD075  |  352.33  |      4  |  2017-06-19 06:01:14  |  WA |
+----------+----------+---------+-----------------------+-----+
| 00AE912  |  231.46  |      2  |  2017-06-16 06:01:16  |  NY |
| 00AE938  |  852.25  |      2  |  2017-06-14 08:24:43  |  NY |
| 00AG510  |  172.27  |      1  |  2017-06-14 08:24:43  |  NY |
| 00AG520  |  359.67  |      6  |  2017-06-14 08:24:43  |  NY |
| 00AG590  |  550.00  |      1  |  2017-06-15 06:31:03  |  CA |
+----------+----------+---------+-----------------------+-----+

So there are multiple rows for each ref, based on the date. I need to select the most recent row. This is what I'm doing, but it gives me an error:

SELECT DISTINCT
    * 
FROM
    inventory 
WHERE
    ref LIKE '00%' 
    AND stock > 0 
GROUP BY
    ref 
ORDER BY
    date

Error:

1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'inventory.scost' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by, Time: 0.319000s

Answer 1

Updated One way of doing it with strict mode turned on

SELECT i.* 
  FROM inventory i JOIN (
    -- fetch the ref and the max date
    SELECT ref, MAX(date) date
      FROM inventory
     WHERE ref LIKE '00%'
       AND stock > 0
     GROUP BY ref
 ) q -- then join back to grab all other columns
    ON i.ref = q.ref
   AND i.date = q.date
 ORDER BY date, ref

or alternatively you can emulate ROW_NUMBER() OVER (PARTITION BY ref ORDER BY date) with session variables since MySQL doesn't yet support window functions

SELECT ref, cost, stock, date, location
  FROM (
    SELECT *, @rn := IF(@g = ref, @rn + 1, 1) rn, @g := ref
      FROM inventory CROSS JOIN (
        SELECT @rn := 1, @g := NULL
      ) v
     WHERE ref LIKE '00%'
       AND stock > 0
     ORDER BY ref, date DESC
 ) q
 WHERE rn = 1
 ORDER BY date, ref

Output:

+---------+------+-------+---------------------+----------+
| ref     | cost | stock | date                | location |
+---------+------+-------+---------------------+----------+
| 00AE938 |  852 |     2 | 2017-06-14 08:24:43 | NY       |
| 00AG510 |  172 |     1 | 2017-06-14 08:24:43 | NY       |
| 00AG520 |  360 |     6 | 2017-06-14 08:24:43 | NY       |
| 00AG590 |  550 |     1 | 2017-06-15 06:31:03 | CA       |
| 00AE912 |  231 |    20 | 2017-06-16 06:01:16 | WA       |
| 00AD075 |  352 |     4 | 2017-06-19 06:01:14 | WA       |
+---------+------+-------+---------------------+----------+

Here is a dbfiddle demo for both queries

Answer 2

SELECT DISTINCT
    * 
FROM
    inventory 
GROUP BY
    ref 
HAVING 
    ref LIKE '00%' 
    AND stock > 0 
ORDER BY
    date desc

Try this.