Select multiple rows with duplicates delimited by unique MySQL meta rows
I need to make a fairly specific query on a MySQL database, I'll start with an example table:
+----+---------------+------------------------------------+----------+
| id | data | pattern_key | hash |
+----+---------------+------------------------------------+----------+
| 1 | {"user":true} | NOTIFICATIONHUB::SYSTEM | ABCDEFGH |
| 2 | {"user":true} | NOTIFICATIONHUB::SYSTEM | ABCDEFGH |
| 3 | {"user":true} | NOTIFICATIONHUB::SYSTEM | ABCDEFGH |
| 4 | {"user":true} | NOTIFICATIONHUB::SYSTEM | HGFEDCBA |
| 5 | {"user":true} | NOTIFICATIONHUB::SYSTEM | HGFEDCBA |
| 6 | {"user":true} | NOTIFICATIONHUB::SYSTEM | OPQRSTUW |
| 7 | {"user":true} | NOTIFICATIONHUB::SYSTEM | ABCDEFGH |
| 8 | {"user":true} | NOTIFICATIONHUB::SYSTEM | ABCDEFGH |
| 9 | {"user":true} | NOTIFICATIONHUB::SYSTEM | IJKLMNOP |
| 10 | {"user":true} | NOTIFICATIONHUB::SYSTEM | ABCDEFGH |
+----+---------------+------------------------------------+----------+
As you can see, I have the column data and pattern_key data that are out of date at the moment. What matters is that the hash column that allows the same values to be used, for example: ABCDEFGH. What I want to do is select 5 rows in descending order, but including duplicates in the hash column and only when they are next to each other. The query result for this table should be:
+----+---------------+------------------------------------+----------+
| id | data | pattern_key | hash |
+----+---------------+------------------------------------+----------+
| 10 | {"user":true} | NOTIFICATIONHUB::SYSTEM | ABCDEFGH |
| 9 | {"user":true} | NOTIFICATIONHUB::SYSTEM | IJKLMNOP |
| 8 | {"user":true} | NOTIFICATIONHUB::SYSTEM | ABCDEFGH |
| 7 | {"user":true} | NOTIFICATIONHUB::SYSTEM | ABCDEFGH |
| 6 | {"user":true} | NOTIFICATIONHUB::SYSTEM | OPQRSTUW |
| 5 | {"user":true} | NOTIFICATIONHUB::SYSTEM | HGFEDCBA |
+----+---------------+------------------------------------+----------+
We have 6 records, not 5 as limited, but line 7 is included because line 8 has the same hash value. This behavior should ignore the number of duplicates as they are one after the other, so if we were ordering ascending id we would get:
+----+---------------+------------------------------------+----------+
| id | data | pattern_key | hash |
+----+---------------+------------------------------------+----------+
| 1 | {"user":true} | NOTIFICATIONHUB::SYSTEM | ABCDEFGH |
| 2 | {"user":true} | NOTIFICATIONHUB::SYSTEM | ABCDEFGH |
| 3 | {"user":true} | NOTIFICATIONHUB::SYSTEM | ABCDEFGH |
| 4 | {"user":true} | NOTIFICATIONHUB::SYSTEM | HGFEDCBA |
| 5 | {"user":true} | NOTIFICATIONHUB::SYSTEM | HGFEDCBA |
| 6 | {"user":true} | NOTIFICATIONHUB::SYSTEM | OPQRSTUW |
| 7 | {"user":true} | NOTIFICATIONHUB::SYSTEM | ABCDEFGH |
| 8 | {"user":true} | NOTIFICATIONHUB::SYSTEM | ABCDEFGH |
| 9 | {"user":true} | NOTIFICATIONHUB::SYSTEM | IJKLMNOP |
+----+---------------+------------------------------------+----------+
since we have 3 ABCDEFGHs (1 unique meta line I think), 2 HGFEDCBAs (2 unique meta lines), 1 OPQRSTUW (3rd unique line), 2 ABCDEFGHs (4 unique metaphors as they are divisible by the first 3 ABCDEFGH with other hashes) and one IJKLMNOP.
I was thinking about a group, but it copies duplicates and I want them to be included in the dataset. Any ideas?
Thanks to @Uueerdo, I came up with this solution (yes, JOIN didn't care):
SET @i := 0;
SET @lastHash := '';
SELECT *
FROM
(SELECT notification_real_id AS id, data, pattern_key, @i := IF(hash <> @lastHash, @i + 1, @i) AS hashGroup, @lastHash := hash AS hash
FROM
( SELECT notifications.id AS notification_real_id,
data,
pattern_key,
hash
FROM notifications
INNER JOIN notifications_users ON notifications.id = notifications_users.notification_id
WHERE notifications_users.user_id = 1) AS subJoin
ORDER BY notification_real_id DESC) AS subQ
WHERE hashGroup <= 5;
source to share
I don't have time to check that this is for sure, but something like this should work:
SET @i := 0;
SET @lastHash := '';
SELECT *
FROM (
SELECT id, data, pattern_key
, @i := IF(hash <> @lastHash, @i + 1, @i) AS hashGroup
, @lastHash := hash
FROM table
ORDER BY id DESC
) AS subQ
WHERE hashGroup <= 5
;
Alternatively (no variables required) (not recommended: not as reliable without data hackers, not as flexible with joins, and most likely much slower):
SELECT *
FROM the_table
WHERE id > (
SELECT MAX(id) AS lastID
FROM the_table
GROUP BY hash
ORDER BY lastID DESC LIMIT 5, 1
)
;
source to share