How to get uniq rows from duplicate records in oracle?

Question

I want to get the fastest quotaBasedService rows from table for each bucket_type. packageSpeed value is in the additionalQuota table with package_id and other fields are in the quotaBased table.

SELECT
      q.ID,
      q.SUBSCRIBER_IDENTIFIER,
      q.BUCKET_TYPE,
      MAX(CASE WHEN ext.PACKAGE_SPEED = 0 THEN 999999 ELSE ext.PACKAGE_SPEED END) AS PACKAGE_SPEED
    FROM PIMS_QUOTA_BASED_SERVICE q
      INNER JOIN PIMS_ADDITIONAL_QUOTA_PACKAGE ext ON ext.PACKAGE_ID = q.ADDITIONAL_QUOTA_PACKAGE_ID
    WHERE q.PERIOD = '201810' AND q.BUCKET_TYPE IS NOT NULL
    GROUP BY
      q.SUBSCRIBER_IDENTIFIER,
      q.BUCKET_TYPE,
      q.ID
    ORDER BY SUBSCRIBER_IDENTIFIER, BUCKET_TYPE, PACKAGE_SPEED DESC

here is the sql result quotaBasedId, subs_identifier, bucket_type, speed

108337,subscriber1@domain,Extra,999999
108345,subscriber1@domain,Extra,5120
108334,subscriber1@domain,Extra1,999999
108342,subscriber1@domain,Extra1,5120
108336,subscriber1@domain,Extra2,999999
108344,subscriber1@domain,Extra2,5120
108335,subscriber1@domain,Extra4,999999
108343,subscriber1@domain,Extra4,5120
108253,subscriber2@domain,Extra,999999
108261,subscriber2@domain,Extra,5120
108250,subscriber2@domain,Extra1,999999
108258,subscriber2@domain,Extra1,5120
108252,subscriber2@domain,Extra2,999999
108260,subscriber2@domain,Extra2,5120
108251,subscriber2@domain,Extra4,999999
108259,subscriber2@domain,Extra4,5120

my expectation is here

108337,subscriber1@domain,Extra,999999
108334,subscriber1@domain,Extra1,999999
108336,subscriber1@domain,Extra2,999999
108335,subscriber1@domain,Extra4,999999
108253,subscriber2@domain,Extra,999999
108250,subscriber2@domain,Extra1,999999
108252,subscriber2@domain,Extra2,999999
108251,subscriber2@domain,Extra4,999999

Answer 1

or set MAX ID so, you wil get the ID too

SELECT
      MAX(q.ID),
      q.SUBSCRIBER_IDENTIFIER,
      q.BUCKET_TYPE,
      MAX(CASE WHEN ext.PACKAGE_SPEED = 0 THEN 999999 ELSE ext.PACKAGE_SPEED END) AS PACKAGE_SPEED
    FROM PIMS_QUOTA_BASED_SERVICE q
      INNER JOIN PIMS_ADDITIONAL_QUOTA_PACKAGE ext ON ext.PACKAGE_ID = q.ADDITIONAL_QUOTA_PACKAGE_ID
    WHERE q.PERIOD = '201810' AND q.BUCKET_TYPE IS NOT NULL
    GROUP BY
      q.SUBSCRIBER_IDENTIFIER,
      q.BUCKET_TYPE,
    ORDER BY SUBSCRIBER_IDENTIFIER, BUCKET_TYPE, PACKAGE_SPEED DESC