Thread: Suggestion. Optional local ORDER BY clause for DISTINCT ON
Gents, I have a suggestion for DISTINCT ON clause syntax.
Determines the precedence within each DISTINCT ON group (i.e. the ‘first’ row to be picked)
Motivation
• Using the query-wide ORDER BY clause to determine which record to pick mixes two unrelated concerns, ‘first’ row selection and result-set ordering. This may be confusing;
• The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). This may cause inconvenience and require nesting as a sub-query to order the result-set.
Pros
• Backward compatibility. If the local ORDER BY clause is missing then the current rules shall apply;
• Familiar and consistent syntax and semantics, the same as in *_agg functions;
• Clear distinction of first row selection and result-set ordering;
• Good readability;
• The DISTINCT ON expression(s) do not have to match the leftmost ORDER BY expression(s).
Cons
• Possible extra verbosity
Best regards,
Stefan
Stefan Stefanov <stefanov.sm@abv.bg> writes: > Gents, I have a suggestion for DISTINCT ON clause syntax. > DISTINCT ON (expression(s) [ORDER BY expression(s)]) > Determines the precedence within each DISTINCT ON group (i.e. the ‘first’ row to be picked) > Motivation > • Using the query-wide ORDER BY clause to determine which record to pick mixes two unrelated concerns, ‘first’ row selectionand result-set ordering. This may be confusing; > • The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). This may cause inconvenience and requirenesting as a sub-query to order the result-set. Since you can get the desired behavior with a sub-select, I'm not especially excited about extending DISTINCT ON. If it weren't such a nonstandard kluge, I might feel differently; but it's not an area that I think we ought to put more effort into. regards, tom lane