Distinct Column gives me incorrect results : I have DISTINCT and wanted to use MIN and MAX functions in queries.

Just using DISTINCT it gives me proper results count: 1348

But if I just add MIN() and MAX() Function in query the query just returns one record having MAX column.
I want all those records proper but need to find MAX amount from price column as well.


Here is my query:

    SELECT DISTINCT a.*, (a.price * 1) as calculatedPrice, py.package_id as py_package_id
    FROM qd_posts as a
    INNER JOIN qd_categories as c ON c.id=a.category_id AND c.active=1
    LEFT JOIN qd_categories as cp ON cp.id=c.parent_id AND cp.active=1
    LEFT JOIN (SELECT MAX(id) max_id, post_id FROM qd_payments WHERE active=1 GROUP BY post_id) mpy ON mpy.post_id = a.id AND a.featured=1
    LEFT JOIN qd_payments as py ON py.id=mpy.max_id
    LEFT JOIN qd_packages as p ON p.id=py.package_id
    WHERE a.country_code = 'CA' AND (a.verified_email = 1 AND a.verified_phone = 1) AND a.archived != 1 AND a.deleted_at IS NULL
    ORDER BY p.lft DESC, a.created_at DESC

After adding MAX to query

    SELECT DISTINCT a.*, (a.price * 1) as calculatedPrice, py.package_id as py_package_id, MAX(a.price) as maxprice
    FROM qd_posts as a
    INNER JOIN qd_categories as c ON c.id=a.category_id AND c.active=1
    LEFT JOIN qd_categories as cp ON cp.id=c.parent_id AND cp.active=1
    LEFT JOIN (SELECT MAX(id) max_id, post_id FROM qd_payments WHERE active=1 GROUP BY post_id) mpy ON mpy.post_id = a.id AND a.featured=1
    LEFT JOIN qd_payments as py ON py.id=mpy.max_id
    LEFT JOIN qd_packages as p ON p.id=py.package_id
    WHERE a.country_code = 'CA' AND (a.verified_email = 1 AND a.verified_phone = 1) AND a.archived != 1 AND a.deleted_at IS NULL
    ORDER BY p.lft DESC, a.created_at DESC


Not sure what to do next.