WITH cte AS ( SELECT DISTINCT n.corporateNumber corporateNumber, n.name name, pc.is_paid_company is_paid_company FROM nta_cnps n LEFT OUTER JOIN industry_classification_link il ON n.corporateNumber = il.corporate_number LEFT OUTER JOIN paid_companies pc ON n.corporateNumber = pc.corporate_number WHERE (n.prefectureCode IN ()) AND () ORDER BY pc.is_paid_company DESC ) SELECT *, (SELECT COUNT(*) FROM cte) AS number_of_companies FROM cte ORDER BY cte.corporateNumber LIMIT 0, 20