Q:

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

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

A:

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;

