Get 10 latest records per category even some has less then 10

Question

This is example of my table :

+-----+-----+------------+--------+-------------+--------------+
| LID | AID | Created    | TypeID | PaymentDate | PaymentValue |
+-----+-----+------------+--------+-------------+--------------+
| 1   | 529 | 2017-05-12 | 1      | 2017-05-12  | 100          |
+-----+-----+------------+--------+-------------+--------------+
| 2   | 529 | 2018-04-10 | 4      | 2018-04-10  | 200          |
+-----+-----+------------+--------+-------------+--------------+
| 3   | 441 | 2014-01-23 | 3      | 2014-01-23  | 300          |
+-----+-----+------------+--------+-------------+--------------+
| 4   | 324 | 2017-09-14 | 1      | 2017-09-14  | 400          |
+-----+-----+------------+--------+-------------+--------------+
| 5   | 111 | 2018-05-12 | 0      | 2018-05-12  | 340          |
+-----+-----+------------+--------+-------------+--------------+
| 6   | 529 | 2018-05-12 | 1      | 2018-05-12  | 100          |
+-----+-----+------------+--------+-------------+--------------+
| 7   | 529 | 2018-06-12 | 1      | 2018-05-12  | 100          |
+-----+-----+------------+--------+-------------+--------------+
| 8   | 529 | 2018-07-12 | 1      | 2018-05-12  | 100          |
+-----+-----+------------+--------+-------------+--------------+
| 9   | 529 | 2018-08-12 | 1      | 2018-05-12  | 100          |
+-----+-----+------------+--------+-------------+--------------+
| 10  | 529 | 2018-09-12 | 1      | 2018-05-12  | 100          |
+-----+-----+------------+--------+-------------+--------------+
| 11  | 529 | 2018-01-12 | 1      | 2018-05-12  | 100          |
+-----+-----+------------+--------+-------------+--------------+
| 12  | 529 | 2018-05-14 | 1      | 2018-05-12  | 100          |
+-----+-----+------------+--------+-------------+--------------+
| 13  | 529 | 2018-05-21 | 1      | 2018-05-12  | 100          |
+-----+-----+------------+--------+-------------+--------------+
| 14  | 529 | 2018-03-12 | 1      | 2018-05-12  | 100          |
+-----+-----+------------+--------+-------------+--------------+

Here another table

+-----+-------+
| ID  |caption|
+-----+-------+
| 0   | bad   |
+-----+-------+
| 1   | good  |
+-----+-------+

I need to get 10 latest records per AID. If there less than 10 records for some AID anyway i need to get ten rows and put "No payment date" into PaymentDate and Created fields, Null into TypeID and 0 into PaymentValue. I can get 10 or less latest records with

select *
from (select *,
         (@rn := if(@c = AID, @rn + 1,
                    if(@c := AID, 1, 1)
                   )
         ) as rn
from history cross join
       (select @rn := 0, @c := -1) params
order by AID, Created desc
) t
having rn <= 10;

But i dont know how force mysql to output 10 rows for each AID. Help me please.

Result should be in a form
AID,TypeId,Created,Caption

Sql cannot create records that do not exist. Supplement the missing records in the application logic when you print out the data — Shadow, Apr 15 '18 at 22:14
Does it mean that it is impossible to create query with such output? Thank you for comment @Shadow — John Doe, Apr 15 '18 at 22:18
You might be able to game the system with some wildly inefficient static union subqueries. But that's not what sql is for. You want to present your data in a different way, than it looks like in your db? Then do it in the application layer. — Shadow, Apr 15 '18 at 22:24
What should be in the `lid` field on the null records? Does `PaymentDate` and `created` always non-null in the table? — kc2018, Apr 15 '18 at 23:31
Seriously consider handling issues of data display in application code — Strawberry, Apr 16 '18 at 00:11
@kc2018 `lid` can be null for non-existing records. `PaymentDate` and `created` always non-null. — John Doe, Apr 16 '18 at 15:16

score 0 · Accepted Answer · answered Apr 16 '18 at 00:03

I have done it. This query needs to create a row of 10 records to combine with distinct AID valies in the table. I was able to show the result for Amount and Create date and will leave it to you to continue since you will get the idea. The critical part is to build a table with 10 rows times distinct AID so about 40 rows in table r. Then do a left join to table t which is similar to what you have done. Table t gets a rank of at most 10 records. Any missing rank up to 10 recs will be filled by table r. Coalesce will assign the default values such as 0 fro amount and 'no create date' for date.

http://sqlfiddle.com/#!9/855c21/2

SELECT coalesce(r.aid, t.aid) as aid,
   coalesce(t.paymentvalue, 0) as paymentvalue,
   coalesce(cast(t.created as char), 'no create date') as created
FROM (select * from (
 select 1 as rw union
  select 2 union select 3 
  union select 4 union select 5
  union select 6 union select 7
  union select 8 union select 9
  union select 10) u
 cross join (select distinct aid
     from history) h
   ) as r
LEFT JOIN (
SELECT a.aid, a.paymentvalue, 
  a.created, count(*) rn
FROM history a
JOIN history b 
ON a.aid = b.aid
AND a.created <= b.created
GROUP BY a.aid, a.created
HAVING COUNT(*) <= 10) t
on r.rw=t.rn and r.aid=t.aid
order by aid, created;

What if the op wants top 12? Top 100? (While I appreciate the enthusiasm, I'm not sure you've thought this through) — Strawberry, Apr 16 '18 at 00:10
If you like my answer, please accept it (green button across my answer). Thanks — jose_bacoy, Apr 16 '18 at 15:25

score 0 · Answer 2 · answered Apr 16 '18 at 01:11

I have added RIGHT JOIN to bring in the null rows to top up to 10 (or n) rows per AID. Initially I use SELECT 1 UNION SELECT 2 ... to generate the 10 rows. In order to make it easier to increase the number of rows (say 100), I am trying this idea of generate_series equivalent for mysql. In order for this to work, the number of rows in history table must be equal to greater than the number of rows required per AID.

select t1.lid
,t2.aid 
,coalesce(t1.created, "no created date") as created
,t1.typeID 
,coalesce(t1.paymentdate, "no payment date") as paymentDate
,coalesce(t1.paymentvalue, 0) as paymentValue
,t2.rn 
from
(
 select *,
         (@rn := if(@c = AID, @rn + 1,
                    if(@c := AID, 1, 1)
                   )
         ) as rn
 from history cross join
 (select @rn := 0, @c := -1) params
 order by AID, Created desc
) t1
right join
( select * 
  from (select  distinct aid from history ) h1 
        cross join 
       (select rn  -- generate table with n rows numbered from 1 to n
        from
        (select 
         @num:= 0) init
         cross join
        (select @num  := @num +1  rn
         from history ) t -- assume history has at least 10 rows
         limit 
         10 ) h2 -- n = 10; change it to the number of rows per aid required
    ) t2
    on t1.aid = t2.aid and t1.rn = t2.rn
    order by t2.aid, t2.rn

Get 10 latest records per category even some has less then 10

2 Answers2