PostgreSQL: Table filtering-I am trying to filter a table so I can keep all relevant results (including duplicates) based on the result of one column

Question

I am trying to write a query that will help me filter a table like the following:

order_number | line_type | price | grand_total
---------------------------------------------
     34      | promo     | 4.35  |  86.25
     34      | tax       | 2.50  |  86.25
     34      | shipping  | 3.40  |  86.25
     12      | shipping  | 2.50  |  12.00
     123     | promo     | 8.10  |  34.00
     123     | shipping  | 4.50  |  34.00
     55      | shipping  | 2.00  |  12.00
     55      | tax       | 1.20  |  12.00

The intent is to retain all results associated with an order_number that has a "promo" line_type, but remove all results for order_numbers that have no associated "promo" line_type. Properly filtered, the above table would have order_numbers 12 and 55 removed, while retaining each distinct line_type for order_numbers 34 and 123, as below:

order_number | line_type | price | grand_total
---------------------------------------------
 34          | promo     | 4.35  |  86.25
 34          | tax       | 2.50  |  86.25
 34          | shipping  | 3.40  |  86.25
 123         | promo     | 8.10  |  34.00
 123         | shipping  | 4.50  |  34.00

Is there a specific clause that could help me here?

Answer 1

Analytic functions come in handy here. We can take the count of records having the promotional line_type over each group of orders. Then, retain only records associated with an order having one or more promo line types.

SELECT
    order_number, line_type, price, grand_total
FROM
(
    SELECT *,
        COUNT(CASE WHEN line_type = 'promo' THEN 1 END) OVER
            (PARTITION BY order_number) cnt
    FROM yourTable
) t
WHERE cnt > 0;