I am trying to pull the 5 most recent records from a MySQL database, then group them together by USER, then after they are grouped, get more records until I have 5 unique USER ordered by date. Ideally, I'd like to do this with one query.
Database:
------------- | user | item | ------------- | 1 | aaa | | 3 | bbb | | 1 | ccc | | 2 | ddd | | 3 | iii | | 4 | eee | | 6 | fff | | 5 | ggg | | 1 | hhh | -------------
Right now, I have this query:
SELECT user,GROUP_CONCAT(item)
FROM database
GROUP BY user
ORDER BY date DESC
LIMIT 5
But this gives me 5 unique records with ALL items for each unique USER, like this:
-------------------- | user | items | -------------------- | 1 | aaa,ccc,hhh | | 3 | bbb,iii | | 2 | ddd | | 4 | eee | | 6 | fff | --------------------
In the previous example, User 1 shouldn't have item "hhh" because there are 7 records with 5 unique USERs before that record. Instead, I want it to pull the first 5 records (ending at USER 3 iii), then grouping user 1 and user 3 duplicate records, then getting two more records to reach LIMIT 5, like this:
-------------------- | user | items | -------------------- | 1 | aaa,ccc | | 3 | bbb,iii | | 2 | ddd | | 4 | eee | | 6 | fff | --------------------
Is this possible to do with a single query so I don't have to pull records, combine, then pull more records?
I dont really get why you need this kind of weird selection, but this might help you at least to start with:
SELECT u.user, u.item FROM
(SELECT db.user, group_concat(db.item) as item
FROM (
SELECT user, item
FROM database
ORDER BY date DESC
LIMIT 5
) db
UNION
SELECT user, item
FROM database
WHERE user not in (
SELECT user
FROM database
ORDER BY date DESC
LIMIT 5
) db
ORDER BY date DESC
limit 5) u
LIMIT 5;
Both subselect get the 5 latest results. Then from the first one we get the agrupation and from the other one we get the users to exclude. We union the results and then limit the results as we dont know ( we do, but not for a more general case ) the amount of results we are going to get on the first select.