How to select first two dates from each group in MySQL?

Question

Good day! Suppose there is a given structure below that displays all information from each mac address. The following MAC addresses were listed in the table if they have ordered more than two days.

   mac_address     |       created_at       |  item_id
--------------------------------------------------------
00:11:22:33:44:55  |   2019-06-13 13:42:34  |     1
00:11:22:33:44:55  |   2019-06-13 13:42:34  |     2
00:11:22:33:44:55  |   2019-06-17 12:12:20  |     3
00:11:22:33:44:55  |   2019-06-17 12:12:20  |     4
00:11:22:33:44:55  |   2019-06-21 17:03:36  |     5
00:11:22:33:44:55  |   2019-06-21 17:03:36  |     6
00:11:22:33:44:55  |   2019-06-21 17:03:36  |     7
00:11:22:33:44:55  |   2019-06-21 17:03:36  |     8
00:11:22:33:44:55  |   2019-06-21 17:03:36  |     9
00:11:22:33:44:55  |   2019-06-22 19:28:46  |     10
01:12:23:34:45:56  |   2019-06-15 15:45:53  |     11
01:12:23:34:45:56  |   2019-06-15 15:45:53  |     12
01:12:23:34:45:56  |   2019-06-15 15:45:53  |     13
01:12:23:34:45:56  |   2019-06-16 14:01:45  |     14
01:12:23:34:45:56  |   2019-06-16 14:01:45  |     15
01:12:23:34:45:56  |   2019-06-16 14:01:45  |     16
01:12:23:34:45:56  |   2019-06-16 17:24:27  |     17
01:12:23:34:45:56  |   2019-06-16 17:24:27  |     18
01:12:23:34:45:56  |   2019-07-01 14:13:36  |     19
01:12:23:34:45:56  |   2019-07-01 14:13:36  |     20

But I need to return a result that displays only the first two dates for each mac address group.

   mac_address     |       created_at       |  item_id
--------------------------------------------------------
00:11:22:33:44:55  |   2019-06-13 13:42:34  |     1
00:11:22:33:44:55  |   2019-06-13 13:42:34  |     2
00:11:22:33:44:55  |   2019-06-17 12:12:20  |     3
00:11:22:33:44:55  |   2019-06-17 12:12:20  |     4
01:12:23:34:45:56  |   2019-06-15 15:45:53  |     11
01:12:23:34:45:56  |   2019-06-15 15:45:53  |     12
01:12:23:34:45:56  |   2019-06-15 15:45:53  |     13
01:12:23:34:45:56  |   2019-06-16 14:01:45  |     14
01:12:23:34:45:56  |   2019-06-16 14:01:45  |     15
01:12:23:34:45:56  |   2019-06-16 14:01:45  |     16
01:12:23:34:45:56  |   2019-06-16 17:24:27  |     17
01:12:23:34:45:56  |   2019-06-16 17:24:27  |     18

This is the code that I have tried so far. Unfortunately, it didn't filter the first two dates for each MAC address group.

SELECT ord.mac_address,ord.created_at,oli.item_id
FROM orders as ord 
INNER JOIN order_line_items AS oli ON oli.order_id = ord.id
WHERE mac_address IN (SELECT mac_address FROM orders
GROUP BY mac_address HAVING COUNT(DISTINCT date(created_at)) > 2
AND mac_address <> '')
AND oli.item_id NOT IN
(SELECT id FROM items WHERE deleted_at <> '')
ORDER BY ord.mac_address, date(ord.created_at) ASC;

Thanks

That is your current or desire result? Because you said you want first two dates but show 4. So how you handle duplicates? — Juan Carlos Oropeza, Sep 02 '19 at 05:04
I'm using version 8.0.17. And yes, that's the desired result that also includes duplicate dates from the first two. — dxlvgcw, Sep 02 '19 at 05:15
Then choose the mysql v8 specific solution from the 2nd highest voted answer of the first duplicate. Out of the 3 suggested ranking functions, choose dense_rank(). — Shadow, Sep 02 '19 at 05:20

score -1 · Answer 1 · answered Sep 02 '19 at 05:05

-1

The easiest way to handle this uses ROW_NUMBER:

SELECT mac_address, created_at, item_id
FROM
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY mac_address ORDER BY created_at) rn
    FROM yourTable
) t
WHERE rn <= 2;

answered Sep 02 '19 at 05:05

Tim Biegeleisen

502,043
27
286
360

How to select first two dates from each group in MySQL?

1 Answers1