Using distinct on in subqueries

Question

I noticed that in PostgreSQL the following two queries output different results:

select a.*
from (
    select distinct on (t1.col1)
        t1.*
    from t1
    order by t1.col1, t1.col2
) a
where a.col3 = value
;

create table temp as
select distinct on (t1.col1)
    t1.*
from t1
order by t1.col1, t1.col2
;
select temp.*
from temp
where temp.col3 = value
;

I guess it has something to do with using distinct on in subqueries.

What is the correct way to use distinct on in subqueries? E.g. can I use it if I don't use where statement? Or in queries like

(
select distinct on (a.col1)
    a.*
from a
)
union
(
select distinct on (b.col1)
    b.*
from b
)

Please provide a minimum reproducible example: sample data and desired results, as tabular text. — GMB
– GMB, Commented Sep 11, 2020 at 8:37

GMB · Accepted Answer · 2020-09-11 09:05:33Z

1

In normal situation, both examples should return the same result.

I suspect that you are getting different results because the order by clause of your distinct on subquery is not deterministic. That is, there may be several rows in t1 sharing the same col1 and col2.

If the columns in the order by do not uniquely identify each row, then the database has to make its own decision about which row will be retained in the resultset: as a consequence, the results are not stable, meaning that consecutive executions of the same query may yield different results.

Make sure that your order by clause is deterministic (for example by adding more columns in the clause), and this problem should not arise anymore.

answered Sep 11, 2020 at 9:05

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

dnoeth Over a year ago

Then running query 1 several times should also result in different results:

(     select distinct on (t1.col1)         t1.*     from t1     order by t1.col1, t1.col2 )  except (     select distinct on (t1.col1)         t1.*     from t1     order by t1.col1, t1.col2 )

Collectives™ on Stack Overflow

Using distinct on in subqueries

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related