[SPARK-28385] SELECT DISTINCT ON ( expression [, ...] ) syntax

• Type: Sub-task
• Status: Resolved
• Priority: Major
• Resolution: Won't Fix
• Affects Version/s: 3.0.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Yuming Wang
• Created: 14/Jul/19 14:03
• Updated: 29/Dec/19 07:08
• Resolved: 29/Dec/19 07:08

Description

SELECT DISTINCT ON ( {{expression [, ...] )}} keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY (see above). Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. For example:

SELECT DISTINCT ON (location) location, time, report
    FROM weather_reports
    ORDER BY location, time DESC;

https://www.postgresql.org/docs/11/sql-select.html