Remove Duplicates from select query

Question

SELECT distinct(product_id), id 
FROM stock_move 
WHERE date >= '2019-09-24 00:00:00' 
and date <= '2019-09-24 23:59:59'

with this query, I still, get results like

    |250, 1256
    |250, 1257
    |259, 1258

but I don't want to select duplicates. so from first to rows, I need just one to be selected. How can I aceave this?

Desired output.

    |250, 1256
    |259, 1258

distinct is NOT a function. It always applies to all columns in the SELECT list. Putting parentheses around one column won't change that. distinct (a),b is exactly the same as distinct a, (b) or distinct a,b - you are probably looking for distinct on () — user330315
– user330315, Commented Sep 30, 2019 at 14:06

Laurenz Albe · Accepted Answer · 2019-09-30 14:06:39Z

2

The parentheses in your query are insignificant, the query is identical to

SELECT DISTINCT product_id, id
FROM stock_move
WHERE date >= '2019-09-24 00:00:00' and date <= '2019-09-24 23:59:59';

So duplicates will only be removed if both product_id and id are the same.

To get what you want, use DISTINCT ON:

SELECT DISTINCT ON (product_id) product_id, id
FROM stock_move
WHERE date >= '2019-09-24 00:00:00' and date <= '2019-09-24 23:59:59';

This will output only one row for each product_id.

If you want to have some control over which row is returned, add an ORDER BY clause.

answered Sep 30, 2019 at 14:06

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1