Written by 
A cool standard SQL:2003 feature is the aggregate FILTER clause, which is supported natively by at least these RDBMS: ClickHouse CockroachDB DuckDB Firebird H2 HSQLDB PostgreSQL SQLite Trino YugabyteDB The following aggregate function computes the number of rows per group which satifsy the FILTER clause: SELECT COUNT(*) FILTER (WHERE BOOK.TITLE LIKE ‘A%’), COUNT(*) FILTER (WHERE […]
I’ve blogged about generic ways of getting top 1 or top n per category queries before on this blog. An Oracle specific version in that post used the arcane KEEP syntax: SELECT max(actor_id) KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id), max(first_name) KEEP (DENSE_RANK FIRST ORDER BY c DESC, actor_id), max(last_name) KEEP (DENSE_RANK FIRST ORDER […]