llamamoray Github contribution chart
llamamoray Github Stats
llamamoray Most Used Languages

Activity

05 Sep 2022

Issue Comment

Llamamoray

DISTINCT ON (...) ORDER BY. Order by being ignored

I believe I have found a bug (this is reproduceable in https://shell.duckdb.org/)

Given the table:

CREATE TABLE test(a INTEGER, b INTEGER);
INSERT INTO test VALUES (2, 4), (1, 6), (2, 7), (2, 5), (3, 5), (3, 3); 

The order by in the following queries are being ignored:

SELECT DISTINCT ON(a) a, b FROM test ORDER BY a, b DESC;
┌───┬───┐
│ a ┆ b │
╞═══╪═══╡
│ 1 ┆ 6 │
│ 2 ┆ 4 │
│ 3 ┆ 5 │
└───┴───┘ 
SELECT DISTINCT ON(a) a, b FROM test ORDER BY a, b ASC;
┌───┬───┐
│ a ┆ b │
╞═══╪═══╡
│ 1 ┆ 6 │
│ 2 ┆ 4 │
│ 3 ┆ 5 │
└───┴───┘ 

Whereas I expect the behaviour to be the same as the following (ordering and selecting the first item):

WITH test_rank AS (SELECT *, rank() OVER(PARTITION BY a ORDER BY a, b DESC) as rank from test) 
SELECT DISTINCT a, b from test_rank where rank = 1;
┌───┬───┐
│ a ┆ b │
╞═══╪═══╡
│ 3 ┆ 3 │
│ 1 ┆ 6 │
│ 2 ┆ 4 │
└───┴───┘ 
WITH test_rank AS (SELECT *, rank() OVER(PARTITION BY a ORDER BY a, b ASC) as rank from test) 
SELECT DISTINCT a, b from test_rank where rank = 1;
┌───┬───┐
│ a ┆ b │
╞═══╪═══╡
│ 3 ┆ 3 │
│ 1 ┆ 6 │
│ 2 ┆ 4 │
└───┴───┘ 

Forked On 05 Sep 2022 at 04:22:49

Llamamoray

Thanks, apologies I hadn't seen that in my search.

Commented On 05 Sep 2022 at 04:22:49

Llamamoray

started

Started On 17 Jul 2022 at 10:12:44