sql - Execution order in SELECT statement -


i'm reading a documentation postgresql select statement want find order in sql statements in select processed.

in documentation says:

select retrieves rows 0 or more tables. general processing of select follows: (...)

  1. (...) select distinct on eliminates rows match on specified expressions. (...)
  1. if order clause specified, returned rows sorted in specified order. (...)

it further says (in distinct clause description):

the distinct on expressions interpreted using same rules order (see above). note "first row" of each set unpredictable unless order used ensure desired row appears first. (...)

the distinct on expression(s) must match leftmost order expression(s). order clause contain additional expression(s) determine desired precedence of rows within each distinct on group.

my natural expectation order should modify result of distinct on , since distinct on may (and will) give unpredictable results, - put - useless, if not preceded select query.

what actual execution order of statements in select statement?
in particular: why order processed before distinct on?


i'm not experienced user of postgresql , sql don't know if i'm asking right question (xy problem). if think question should "how distinct on work?" or maybe "does placement of keywords in select statement matter?" or else, please edit question appropriately.

first "order of processing" bit misleading in sql. statements compiled before execution. actual execution might different sql (in fact, execution represented dag -- directed acyclic graph -- looks nothing select syntax).

distinct on postgres extension. quite handy -- letting 1 row per value in parentheses. and, can control row.

let's have data this:

a    b    c a1   3    1 a1   2    2 a1   4    3 a2   6    4 a2   1    5 

you use distinct on when want specify want one row per value. and, let's specify value want. instance, row lowest value of b each a:

select distinct on (a) a, b, c t order a, b asc; 

to row highest value of b each a:

select distinct on (a) a, b, c t order a, b desc; 

the expressions in parentheses need match expressions leading expressions in order by.

you not going feel how works reading documentation. need use it.

this functionality can replaced subquery row_number() , where clause in outer query. why other databases have not adapted similar functionality.


Comments

Popular posts from this blog

resizing Telegram inline keyboard -

command line - How can a Python program background itself? -

php - "cURL error 28: Resolving timed out" on Wordpress on Azure App Service on Linux -