how to get result of some data for every each id?

Question

I have two tables. one is place information. other things is having a some url information with update date data.

I want to get the data maximum 4(limit 4) url order by update_date for every each place_id.

1. place table

  id   place_name 
+----+-------------+
  1         a
  2         b
  3         c
  .         .
  .         .

2. place_url table

  id     place_id          place_url           update_date
+----+-------------+----------------------+-----------------+
  1         1         http://example1.com      2019.01.01
  2         1         http://example2.com      2019.01.02
  3         1         http://example3.com      2019.01.03
  4         2         http://example4.com      2019.01.04
  5         2         http://example5.com      2019.01.06
  6         2         http://example6.com      2019.01.07
  7         2         http://example7.com      2019.01.09
  8         3         http://example8.com      2019.01.11
  .         .                   .                   .
  .         .                   .                   .
  .         .                   .                   .

** I want to get url ever each per place_id limited 4 with ORDER BY update_date DESC**

 id     place_id          place_url           update_date
+----+-------------+----------------------+-----------------+
  1         1         http://example1.com      2019.01.01
  2         1         http://example2.com      2019.01.02
  3         1         http://example3.com      2019.01.03
  4         2         http://example4.com      2019.01.04
  5         2         http://example5.com      2019.01.06
  6         2         http://example6.com      2019.01.07
  7         2         http://example7.com      2019.01.09

  ..
  ...

the basic example code is below.

SELECT p.id, pu.place_id, pu.place_url FROM place AS p
    LEFT JOIN place_url AS pu ON pu.place_id = p.id
    WHERE p.id IN (1,2,3) ORDER BY pu.update_date DESC LIMIT 4;



but the result is not limit 4 with order by update_date each of place_id. just limited total result.

Answer 1

You can try to use a correlated subquery, that counts the later records for a place and checks, that it is less than four.

SELECT p1.id,
       pu1.place_id,
       pu1.place_url
       FROM place AS p1
            LEFT JOIN place_url AS pu1
                      ON pu1.place_id = p1.id
                         AND (SELECT count(*)
                                     FROM place_url pu2
                                     WHERE pu2.palace_id = pu1.place_id
                                           AND (pu2.update_date > pu1.update_date
                                                 OR pu2.update_date = pu1.update_date
                                                    AND pu2.id > pu1.id)) < 4
       WHERE p1.id IN (1,
                       2,
                       3)
       ORDER BY pu1.update_date;