How can I only "pick" (not aggregated) one row if there are duplicate values on specific column?

Question

Right now I have this query:

SELECT DISTINCT
    stock_picking.id          as delivery_order_id,
    sale_order.id             as sale_order_id,
    sale_order.name           as sale_order_name,
    stock_picking.origin      as stock_picking_origin,
    stock_picking.name        as stock_picking_name,
    stock_picking.create_date as stock_picking_create_date,
    sub.count_origin          as sale_order_delivery_order_done_count
FROM
(
    SELECT
        origin,
        COUNT(origin) as count_origin
    FROM stock_picking
    WHERE state = 'done'
    GROUP BY origin
    HAVING COUNT(origin) > 1
    ORDER BY origin
) sub
JOIN sale_order      ON sale_order.name = sub.origin
JOIN account_invoice ON account_invoice.origin = sale_order.name
JOIN stock_picking   ON stock_picking.origin = sale_order.name
WHERE
    account_invoice.create_date >= '04/17/20'       AND
    sale_order.create_date      <= '04/01/20 07:00' AND
    sale_order.create_date      >= '03/01/20'
ORDER BY sale_order.name
;

It returns:

+-------------------+---------------+-----------------+----------------------+--------------------+----------------------------+--------------------------------------+
| delivery_order_id | sale_order_id | sale_order_name | stock_picking_origin | stock_picking_name | stock_picking_create_date  | sale_order_delivery_order_done_count |
+-------------------+---------------+-----------------+----------------------+--------------------+----------------------------+--------------------------------------+
|           2053131 |       5840046 |         3258428 |              3258428 | WH/OUT/1804215     | 2020-03-01 07:10:32.144694 |                                    2 |
|           2071149 |       5840046 |         3258428 |              3258428 | WH/OUT/1819605     | 2020-03-03 18:00:25.208632 |                                    2 |
|           2154480 |       5840046 |         3258428 |              3258428 | WH/OUT/1894584     | 2020-03-11 08:39:33.514114 |                                    2 |
|           2053494 |       5840408 |         3258728 |              3258728 | WH/OUT/1804574     | 2020-03-01 07:41:26.728154 |                                    2 |
|           2105133 |       5840408 |         3258728 |              3258728 | WH/OUT/1849288     | 2020-03-07 13:59:10.049683 |                                    2 |
|           2192492 |       5840408 |         3258728 |              3258728 | WH/OUT/1929553     | 2020-03-13 09:10:26.18469  |                                    2 |
|           2061022 |       5861189 |         3279458 |              3279458 | WH/OUT/1811084     | 2020-03-02 14:37:35.803326 |                                    2 |
|           2170656 |       5861189 |         3279458 |              3279458 | WH/OUT/1909477     | 2020-03-12 08:57:15.434752 |                                    2 |
|           2072002 |       5885577 |         3294059 |              3294059 | WH/OUT/109633      | 2020-03-04 02:44:03.302924 |                                    2 |
|           2130430 |       5885577 |         3294059 |              3294059 | WH/OUT/114259      | 2020-03-10 03:13:58.33838  |                                    2 |
+-------------------+---------------+-----------------+----------------------+--------------------+----------------------------+--------------------------------------+

I want to make sure that the column sale_order_id is unique, but picked from the least delivery_order_id and not aggregated.

I want to have a result like this:

+-------------------+---------------+-----------------+----------------------+--------------------+----------------------------+--------------------------------------+
| delivery_order_id | sale_order_id | sale_order_name | stock_picking_origin | stock_picking_name | stock_picking_create_date  | sale_order_delivery_order_done_count |
+-------------------+---------------+-----------------+----------------------+--------------------+----------------------------+--------------------------------------+
|           2053131 |       5840046 |         3258428 |              3258428 | WH/OUT/1804215     | 2020-03-01 07:10:32.144694 |                                    2 |
|           2053494 |       5840408 |         3258728 |              3258728 | WH/OUT/1804574     | 2020-03-01 07:41:26.728154 |                                    2 |
|           2061022 |       5861189 |         3279458 |              3279458 | WH/OUT/1811084     | 2020-03-02 14:37:35.803326 |                                    2 |
|           2072002 |       5885577 |         3294059 |              3294059 | WH/OUT/109633      | 2020-03-04 02:44:03.302924 |                                    2 |
+-------------------+---------------+-----------------+----------------------+--------------------+----------------------------+--------------------------------------+

score 1 · Accepted Answer · answered May 14 '20 at 11:29

1

You can use distinct on. Your query is complicated, so I'll encapsulate it in a CTE:

with q as (
      . . . 
     )
select distinct on (sale_order_id) q.*
from q
order by sale_order_id, delivery_order_id;

answered May 14 '20 at 11:29

Gordon Linoff

1,242,037
58
646
786

Thanks.... `DISTINCT ON` means this query only works for PostgreSQL right? – notalentgeek May 14 '20 at 12:02
@notalentgeek . . . Yes. It is a handy Postgres extension. In most other databases you would use `row_number()`. – Gordon Linoff May 14 '20 at 13:46

How can I only "pick" (not aggregated) one row if there are duplicate values on specific column?

1 Answers1