PostgreSQL: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Question

Let's say I have the following PostgreSQL table called products:

CREATE TABLE IF NOT EXISTS mytable (
    id serial NOT NULL PRIMARY KEY,
    label VARCHAR(50) NOT NULL,
    info jsonb NOT NULL,
    created_at timestamp NOT NULL DEFAULT now()
);

Here is some test data. Note my actual table has millions of records.

INSERT INTO products (label, info) VALUES ('a', '[1, 2, 3]');
INSERT INTO products (label, info) VALUES ('a', '[1, 2, 3]');
INSERT INTO products (label, info) VALUES ('c', '[1, 2, 3]');
INSERT INTO products (label, info) VALUES ('c', '[1, 2, 3]');
INSERT INTO products (label, info) VALUES ('b', '[1, 2, 3]');

I want to write a query that grabs distinct labels and orders the records by the created_at field. My first instinct would be to write the following query:

SELECT DISTINCT ON (label) * FROM products ORDER BY created_at DESC;

However, this fails with the following error:

ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Looks like I can fix this using SQL subqueries:

SELECT * FROM (
    SELECT DISTINCT ON (label) * FROM products
) AS subquery ORDER BY created_at DESC;

which generates the following expected result:

 id | label |   info    |         created_at
----+-------+-----------+----------------------------
  5 | b     | [1, 2, 3] | 2022-11-14 03:32:23.245669
  3 | c     | [1, 2, 3] | 2022-11-14 03:32:23.242813
  1 | a     | [1, 2, 3] | 2022-11-14 03:32:23.239791

Is this the best way to fix this issue? Or is there a faster way to query this data? Note, I mention above how my actual table has millions of records so I want to come up with the best query possible.

Your last query selects an arbitrary row for each label, then sorts that arbitrary selection by created_at. If that is what you want to do, then that query is probably the best way to do it. — jjanes
– jjanes, Commented Nov 14, 2022 at 13:51

Erwin Brandstetter · Accepted Answer · 2025-07-04 01:41:02Z

13

Leading ORDER BY expressions must cover all DISTINCT ON expressions before you can append more (just rephrasing that error msg):

SELECT DISTINCT ON (label) * FROM products ORDER BY label, created_at DESC;

See:

Select first row in each GROUP BY group?

It's unclear whether you want created_at ASC or created_at DESC: you show the latter, but the results you call "expected" match the former - because your fix does not do what you seem to think it's doing.

To sort resulting (distinct) rows by created_at DESC, you have to run an outer SELECT with a different sort order:

SELECT *
FROM  (
   SELECT DISTINCT ON (label) *
   FROM   products
   ORDER  BY label, created_at DESC
   ) sub
ORDER  BY created_at DESC;

See:

PostgreSQL DISTINCT ON with different ORDER BY

For a big table, be sure to have an index on (label, created_at) or (label, created_at DESC), respectively.

my actual table has millions of records so I want to come up with the best query possible.

There may be (much) faster solutions depending on undisclosed details. Most importantly:

How many distinct "labels" for how many rows in total?
Is there a separate table with one row per (relevant) distinct label?
Do you actually need SELECT *, or is SELECT label, created_at all you need?

edited Jul 4 at 1:41

answered Nov 14, 2022 at 4:07

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

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

2 Comments

Johnny Metz Over a year ago

The query you proposed generates the wrong result. It's not ordered by created_at in descending order.

Erwin Brandstetter Over a year ago

Consider the additions. Careful not to confound two distinct questions: which row to pick per label, and how to sort resulting (distinct) rows.

hit3k · Accepted Answer · 2025-03-10 00:37:54Z

0

Technically you can re-write your query, replace SELECT DISTINCT ON with an aggregate function like MAX and GROUP BY. Then you'll be able to group and sort at any order you like.

answered Mar 10 at 0:37

hit3k

1,5231 gold badge16 silver badges21 bronze badges

Collectives™ on Stack Overflow

PostgreSQL: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related