MySQL - Tricky LIMIT for every WHERE_IN problem
I want to do the following (see pseudo code); I want to select 4 rows for each gd.id (7, 11 or 9). I misused the limit because it is only 4 lines. Anyone have an idea on how to modify this query to achieve my goal?
SELECT gd.gid, gd.aid, li.ads, li.til
FROM gd
JOIN li ON li.a_id = gd.aid
WHERE gd.gid
IN (
'7', '11', '9'
)
ORDER BY li.timestamp DESC
LIMIT 4 #FOR EACH ;-)
Thank!
Ice
ps Maybe sometype group_by?
Ok, now I am posting this second answer to understand the relationship between your tables.
CREATE TABLE gd (
aid INT AUTO_INCREMENT PRIMARY KEY,
gid INT
);
INSERT INTO gd (gid) VALUES
(7), (7), (7), -- fewer than four rows
(9), (9), (9), (9), -- exactly four rows
(11), (11), (11), (11), (11); -- greater than four rows
CREATE TABLE li (
a_id INT AUTO_INCREMENT PRIMARY KEY,
ads VARCHAR(10),
til VARCHAR(10),
`timestamp` TIMESTAMP
);
INSERT INTO li (ads, til, `timestamp`) VALUES
('foo1', 'bar1', '2008-01-01'),
('foo2', 'bar2', '2008-02-01'),
('foo3', 'bar3', '2008-03-01'),
('foo4', 'bar4', '2008-04-01'),
('foo5', 'bar5', '2008-05-01'),
('foo6', 'bar6', '2008-06-01'),
('foo7', 'bar7', '2008-07-01'),
('foo8', 'bar8', '2008-08-01'),
('foo9', 'bar9', '2008-09-01'),
('foo10', 'bar10', '2008-10-01'),
('foo11', 'bar11', '2008-11-01'),
('foo12', 'bar12', '2008-12-01');
So, you want the top four rows per value gd.gid
, depending on the value timestamp
in the linked table li
.
SELECT g1.gid, g1.aid, l1.ads, l1.til, l1.`timestamp`
FROM gd AS g1
INNER JOIN li AS l1 ON (g1.aid = l1.a_id)
LEFT OUTER JOIN (
gd AS g2 INNER JOIN li AS l2 ON (g2.aid = l2.a_id)
) ON (g1.gid = g2.gid AND l1.`timestamp` <= l2.`timestamp`)
WHERE g1.gid IN ('7', '11', '9')
GROUP BY g1.aid
HAVING COUNT(*) <= 4
ORDER BY g1.gid ASC, l1.`timestamp` DESC;
The conclusion is as follows:
+------+-----+-------+-------+---------------------+
| gid | aid | ads | til | timestamp |
+------+-----+-------+-------+---------------------+
| 7 | 3 | foo3 | bar3 | 2008-03-01 00:00:00 |
| 7 | 2 | foo2 | bar2 | 2008-02-01 00:00:00 |
| 7 | 1 | foo1 | bar1 | 2008-01-01 00:00:00 |
| 9 | 7 | foo7 | bar7 | 2008-07-01 00:00:00 |
| 9 | 6 | foo6 | bar6 | 2008-06-01 00:00:00 |
| 9 | 5 | foo5 | bar5 | 2008-05-01 00:00:00 |
| 9 | 4 | foo4 | bar4 | 2008-04-01 00:00:00 |
| 11 | 12 | foo12 | bar12 | 2008-12-01 00:00:00 |
| 11 | 11 | foo11 | bar11 | 2008-11-01 00:00:00 |
| 11 | 10 | foo10 | bar10 | 2008-10-01 00:00:00 |
| 11 | 9 | foo9 | bar9 | 2008-09-01 00:00:00 |
+------+-----+-------+-------+---------------------+
source to share
the usual approach:
(SELECT * FROM table WHERE key = X LIMIT 4) UNION ALL (SELECT * FROM table WHERE key = Y LIMIT 4) UNION ALL (SELECT * FROM table WHERE key = Z LIMIT 4) ORDER BY ... LIMIT ...
Note that this materializes each subproblem in a seductive one, so it is not very efficient if your outer LIMIT is low and your inner LIMIT is high.
source to share