Regarding the auto-formatting functionality in SQL Developer (right-click SQL >> Format):
I've configured the Advanced Format preferences so that auto-formatted queries look like this:
SELECT *
FROM ( SELECT cities.*,
ROW_NUMBER() OVER(
PARTITION BY country
ORDER BY population DESC
) rn,
COUNT(*) OVER(
PARTITION BY country
) ct_cities
FROM cities
) t
WHERE t.rn = 1
AND t.ct_cities > 1;
Screenshot of preferences
That's a good start. But what I actually want is something like this:
SELECT *
FROM (SELECT cities.*,
ROW_NUMBER() OVER(PARTITION BY country ORDER BY population DESC) rn,
COUNT(*) OVER(PARTITION BY country) ct_cities
FROM cities
) t
WHERE t.rn = 1
AND t.ct_cities > 1
The main difference is the analytic/window functions. The auto-formatted analytic functions have been split up over multiple lines.
ROW_NUMBER() OVER(
PARTITION BY country
ORDER BY population DESC
) rn,
Whereas I want the analytic function all on a single line:
ROW_NUMBER() OVER(PARTITION BY country ORDER BY population DESC) rn,
Is there a way to auto-format queries so that analytic functions are kept intact on a single line?
Thanks.