3 ms·

I believe some of the window functions[0] (specifically, row_number) will do what you want. Honestly though, these might not be a readability improvement over

I believe some of the window functions[0] (specifically, row_number) will do what you want. Honestly though, these might not be a readability improvement over subqueries. [0] https://www.postgresql.org/docs/11/functions-window.html https://www.postgresql.org/docs/11/functions-window.html Edit: Just tested it, it works but I wouldn't call it pretty. create table testing(id int, grp int, other varchar2(255)); insert into testing values (1, 1, ''); insert into testing values (2, 1, ''); insert into testing values (3, 1, ''); insert into testing values (9, 2, ''); insert into testing values (10, 2, ''); with cte as ( select grp, id, row_number() over (partition by grp order by id) as rn from testing ) select grp, id from cte where rn = 1;

Tostino 8y ago
Another solution for PG which i've used many times: select distinct on (grp) grp, id from testing order by grp, id;
- Mister_Snuggles 8y ago
  Neat! It doesn't work on Oracle, but you implied that it's PostgreSQL-specific so I somewhat expected that. It's a lot cleaner than mine and, even though it's not something I've seen before, it's fairly obvious what it's going to do. When I did my testing I did it on Oracle since I have it available at work. For my non-work projects I use PostgreSQL and, for the most part, find that I can flip between the two database without too much trouble. Of course, this also means that I don't know about some PostgreSQL niceties like what you showed.
  - Tostino 8y ago
    Very true, it's a nice PG specific extension of the SQL standard. Would be nice if some of these types of extensions made it back into the standard to increase the likelyhood other DB's you use would also support it. It's syntactic sugar, but it is a whole lot more readable in some cases than a window function. It's nice to not need a sub query or cte to express it.
- crescentfresh 8y ago
  Holy shit. Thank you!
dkarl 8y ago
You can also express this example using the first_value window function: select distinct grp, first_value(id) over (partition by grp order by id) as rn from testing;
- Mister_Snuggles 8y ago
  The only challenge here is that you can't bring out other fields from the table. Building on my previous example: insert into testing values (8, 2, 'stuff'); select distinct grp, first_value(id) over (partition by grp order by id) as rn, other from testing; This returns two rows for grp = 2, whereas adding the 'other' column to my first attempt (both in the CTE and the main query) works as expected and only returns one row for each grp. This would work properly within the CTE, but then you're joining to the CTE instead of just querying it. There are many ways to solve this problem. My ideal solution would be something like this that gets rid of the CTE: select grp, id, row_number() over (partition by grp order by id) as rn, other from testing where rn = 1 But that doesn't work. Oracle only allows window functions in the SELECT clause, not in the WHERE clause, plus you can't reference the column alias anyway.
Someone 8y ago
I think what paddy_m complains about is SQL’s tendency to require copy-paste programming. For example, a phrase like row-number() over (partition by grp order by id) might return in many, many of your stored procedures, but there’s no way to only write it once in SQL. The same thing effectively applies to that common table expression. There, you can create a view, and just write select grp, id from grouped_tests where rn = 1; , but many SQL query planners won’t optimize queries that well across views, so you end up copy-pasting the CTE, or, worse, not even doing that because you have to write an even uglier query to get decent performance.
mping 8y ago
Bear in mind that CTEs act as an optimization fence, so the filter (rn=1) wont be pushed to the CTE. The CTE will be materialized and only then the filter will be applied. I believe pg12 will change this behaviour.
- darksaints 8y ago
  That is only in postgres. The optimization fence is not part of the standard, was only created in postgres for simplification of implementation, and is likely to be removed soon (version 12). https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...

Tostino 8y ago

Another solution for PG which i've used many times: select distinct on (grp) grp, id from testing order by grp, id;

Mister_Snuggles 8y ago
Neat! It doesn't work on Oracle, but you implied that it's PostgreSQL-specific so I somewhat expected that. It's a lot cleaner than mine and, even though it's not something I've seen before, it's fairly obvious what it's going to do. When I did my testing I did it on Oracle since I have it available at work. For my non-work projects I use PostgreSQL and, for the most part, find that I can flip between the two database without too much trouble. Of course, this also means that I don't know about some PostgreSQL niceties like what you showed.
- Tostino 8y ago
  Very true, it's a nice PG specific extension of the SQL standard. Would be nice if some of these types of extensions made it back into the standard to increase the likelyhood other DB's you use would also support it. It's syntactic sugar, but it is a whole lot more readable in some cases than a window function. It's nice to not need a sub query or cte to express it.
crescentfresh 8y ago
Holy shit. Thank you!

dkarl 8y ago

You can also express this example using the first_value window function: select distinct grp, first_value(id) over (partition by grp order by id) as rn from testing;

Mister_Snuggles 8y ago
The only challenge here is that you can't bring out other fields from the table. Building on my previous example: insert into testing values (8, 2, 'stuff'); select distinct grp, first_value(id) over (partition by grp order by id) as rn, other from testing; This returns two rows for grp = 2, whereas adding the 'other' column to my first attempt (both in the CTE and the main query) works as expected and only returns one row for each grp. This would work properly within the CTE, but then you're joining to the CTE instead of just querying it. There are many ways to solve this problem. My ideal solution would be something like this that gets rid of the CTE: select grp, id, row_number() over (partition by grp order by id) as rn, other from testing where rn = 1 But that doesn't work. Oracle only allows window functions in the SELECT clause, not in the WHERE clause, plus you can't reference the column alias anyway.

Someone 8y ago

I think what paddy_m complains about is SQL’s tendency to require copy-paste programming. For example, a phrase like row-number() over (partition by grp order by id) might return in many, many of your stored procedures, but there’s no way to only write it once in SQL. The same thing effectively applies to that common table expression. There, you can create a view, and just write select grp, id from grouped_tests where rn = 1; , but many SQL query planners won’t optimize queries that well across views, so you end up copy-pasting the CTE, or, worse, not even doing that because you have to write an even uglier query to get decent performance.

mping 8y ago

Bear in mind that CTEs act as an optimization fence, so the filter (rn=1) wont be pushed to the CTE. The CTE will be materialized and only then the filter will be applied. I believe pg12 will change this behaviour.

darksaints 8y ago
That is only in postgres. The optimization fence is not part of the standard, was only created in postgres for simplification of implementation, and is likely to be removed soon (version 12). https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-allow-user-control-of-cte-materialization-and-change-the-default-behavior/ https://www.depesz.com/2019/02/19/waiting-for-postgresql-12-...