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?

+1


source to share


2 answers


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 | 
+------+-----+-------+-------+---------------------+

      

+5


source


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.

0


source







All Articles