 <p>I don't have access to Oracle, but I believe something along these lines should work...</p> <pre><code>WITH ranked_data AS ( SELECT COUNT(DISTINCT product_type) OVER (PARTITION BY brand_id) AS brand_rank, MIN(product_type) OVER (PARTITION BY brand_id) AS first_product_type, * FROM yourTable ) SELECT * FROM ranked_data ORDER BY brand_rank, first_product_type, brand_id, product_type, product_description </code></pre> <p><br/></p> <p>An alternative is to JOIN on to a sub-query to calculate the two sorting fields.</p> <pre><code>SELECT yourTable.* FROM yourTable INNER JOIN ( SELECT brand_id, COUNT(DISTINCT product_type) AS brand_rank, MIN(product_type) AS first_product_type, FROM yourTable GROUP BY brand_id ) AS brand_summary ON yourTable.brand_id = brand_summary.brand_id ORDER BY brand_summary.brand_rank, brand_summary.first_product_type, yourTable.brand_id, yourTable.product_type, yourTable.product_description </code></pre>