Home>

Environment: MYSQL5.7
There is an accounts table (id, name) and a clients table (id, name), and a recommend table (id, account_id, client_id, recommend_date, open_date) that cross joins them,
I want to get a maximum of 10 records for each account from the recommend table. If id is specified, I think that it will be as follows, but is there a way to get up to 10 pieces of data in the recommend table of all accounts without specifying id one by one?
* Recommend.recommend_date is NULL at the time of creation, and the date/time is entered at the time of SELECT.
If open_date does not contain a date, it will be subject to SELECT again.

select *
from recommend
left join accounts on accounts.id = recommend.account_id
left join clients on clients.id = recommend.client_id
where recommend.account_id = 8206 and open_date is null
order by recommend_date, recommend.client_id desc
limit 10
;
  • Answer # 1

      

    accounts table and clients table, recommend table cross-joined between them

    If it is

    , I should use 10 clients and cross join with accounts instead of using recommend.

  • Answer # 2

    * Additional notes.

    limitis attached and PostgreSQL, right?

    For guidance,

    Create serial number for each account_id with row_number () (recommend_order)

    Subtract this query as a subquery and extract rows where recommend_order is 10 or less

    with query1 as (
      Select *-← * Stop and list the necessary columns
        , row_number () over (pertition by recommend.account_id order by recommend_date, recommend.client_id desc) recommend_order
      from recommend
        left join accounts on accounts.id = recommend.account_id
        left join clients on clients.id = recommend.client_id
      order by recommend_date, recommend.client_id desc
    )
    select
      *-<-Also stop *.
    from
      query1
    where recommend_order<= 10;


    It wasn't actually moving because the environment wasn't ready. Please check the grammar and move it.


    Add from here.
    On the other hand, MySQL5.7.
    As many others have pointed out, the syntax and functions that can be used in SQL differ considerably depending on the RDBS, so if you do not specify the product name, you will get such an unusual answer. So be careful afterwards.

    With

    , you can't use the window function under MySQL8.0.
    If you still try to write it, it will look like this.
    (We verified that you can get up to 10 accounts_id.
    Also, since it is not related to the essence of the problem, accouts and clients join were omitted)

    select
      query.account_id
      , query.client_id
      , query.recommend_date
      , query.row_number
    from (
      select
        main_r.account_id
      , main_r.client_id
      , main_r.recommend_date
      , (select
          count (*)
        from
          recommend sub_r1
          where sub_r1.account_id = main_r.account_id and (
            (sub_r1.recommend_date<main_r.recommend_date)
        or (sub_r1.recommend_date = main_r.recommend_date and sub_r1.client_id>main_r.client_id))
         ) +1 row_number
      from
        recommend main_r
      order by row_number) query
    where query.row_number<= 10
    order by account_id, row_number
    ;


    A subquery with count (*) is processing row_number. It is difficult to write the comparison conditions separately.
    In this case, the condition is still simple, so it is defeated by the count and where clauses, but if it becomes more complicated aggregation condition, black magic SQL will be born.

    Creating a query that retrieves the top N items from a group has really been a challenge,
    There are various StackOverflow questions, such as this one or the like, but every question is answered whispering, "The query is not elegant."
    I honestly think that it is much better for mental health to read this number by numbering one line at the destination where the query was fetched and to skip the 11th and subsequent items of the same account_id. (Maybe you use PHP or rails)
    This time, you can solve the problem with the above one, but I think that the actual query you make is not one, and it is certainly easier to do the aggregation work at the source.

    For verification below.

    CREATE TABLE `recommend` (
      `account_id` int (11) DEFAULT NULL,
      `client_id` int (11) DEFAULT NULL,
      `recommend_date` varchar (8) DEFAULT NULL
    ) ENGINE = InnoDB DEFAULT CHARSET = latin1;
    insert into db.recommend values ​​(1,1, '20180101');
    insert into db.recommend values ​​(1,1, '20180201');
    insert into db.recommend values ​​(1,1, '20180301');
    insert into db.recommend values ​​(1,2, '20180101');
    insert into db.recommend values ​​(1,2, '20180201');
    insert into db.recommend values ​​(1,2, '20180301');
    insert into db.recommend values ​​(1,3, '20180101');
    insert into db.recommend values ​​(1,3, '20180201');
    insert into db.recommend values ​​(1,3, '20180301');
    insert into db.recommend values ​​(2,1, '20180102');
    insert into db.recommend values ​​(2,1, '20180202');
    insert into db.recommend values ​​(2,1, '20180302');
    insert into db.recommend values ​​(2,2, '20180102');
    insert into db.recommend values ​​(2,2, '20180202');
    insert into db.recommend values ​​(2,2, '20180302');
    insert into db.recommend values ​​(2,3, '20180102');
    insert into db.recommend values ​​(2,3, '20180202');
    insert into db.recommend values ​​(2,3, '20180302');
    insert into db.recommend values ​​(1,11, '20180101');
    insert into db.recommend values ​​(1,11, '20180201');
    insert into db.recommend values ​​(1,11, '20180301');
    insert into db.recommend values ​​(1,12, '20180101');
    insert into db.recommend values ​​(1,12, '20180201');
    insert into db.recommend values ​​(1,12, '20180301');
    insert into db.recommend values ​​(1,13, '20180101');
    insert into db.recommend values ​​(1,13, '20180201');
    insert into db.recommend values ​​(1,13, '20180301');
    insert into db.recommend values ​​(2,11, '20180102');
    insert into db.recommend values ​​(2,11, '20180202');
    insert into db.recommend values ​​(2,11, '20180302');
    insert into db.recommend values ​​(2,12, '20180102');
    insert into db.recommend values ​​(2,12, '20180202');
    insert into db.recommend values ​​(2,12, '20180302');
    insert into db.recommend values ​​(2,13, '20180102');
    insert into db.recommend values ​​(2,13, '20180202');
    insert into db.recommend values ​​(2,13, '20180302');