0

This is my query

SELECT * FROM( 
    SELECT orderid, duration, is_renew, bundle_id, service_id, object_name, pkg_id_fk, disconnect_time, DATE_ADD(paid_on, INTERVAL duration HOUR) AS expires
    FROM wtb_orders WHERE username= :username AND is_paid=1 AND is_revoked=0
    HAVING (expires + INTERVAL :retry_rate DAY) >= NOW()
    ORDER BY expires DESC LIMIT 300)
    as tmp GROUP BY 
    tmp.pkg_id_fk, 
    tmp.bundle_id,
    tmp.service_id

I want to:

  • Keep duplicate pkg_id_fk if the other duplicate has a different duration
  • Keep duplicate bundle_id if the other duplicate has a different duration
  • Keep duplicate service_id if the other duplicate has a different duration

Everything else about the query is correct and I don't want to change anything else. It's just that group_by remove duplicates and I don't know how to keep certain duplicates from being removed.

if you need to see the DB schema, here, you just need to look at wtb_orders

3
  • Add duration to GROUP BY expression...
    – Akina
    Apr 19, 2020 at 20:49
  • @Akina would that really work? wouldn't group_by already have removed the duplicates? Or wouldn't it remove bundle_id and service_id having the same duration?
    – Lynob
    Apr 19, 2020 at 21:49
  • I don't understand your words. While GROUP BY pkg_id_fk, bundle_id, service_id, duration, if at least one field differs you'll obtain separate grouip. PS. And looking at if you need to see the DB schema, here, you just need to look at wtb_orders I see only Unknown column 'tmp.pkg_id_fk' in 'group statement'. And do not see the query text posted. Please synchronize...
    – Akina
    Apr 20, 2020 at 5:05

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.