On Wed, 15 Feb 2017 12:06:58 +0100
Marek Wieckowski <wiec...@gmail.com> wrote:
> The thing is that in principle there is nothing wrong with using
> test.xxx fields in the subselect: there really should be no
> difference whether you use them in "where" or "order by"... The fact
> that sqlite does not allow them to be used in ORDER BY (while
> allowing in SELECT and WHERE) imho is simply a bug.
The SQL standard does not allow ORDER BY anywhere except as the last
clause of the main query. ORDER BY does not enter into the logic of
the query; it's a convenience to the host program receiving the rows.
The idiom
select ...
order by ...
limit 1
can always be replaced with a logical
select min(...) -- or max(...)
The idiom
select ...
limit N
is illogical because nondeterministic. Since we're trading
opinions ;-) mine is that SQL should allow only logical constructs and
should refuse every illogical construct as a syntax error.
There was kind of hole in standard SQL in that there was no convenient
way to express the idea of TOP N rows or the Nth row with a particular
ranking. That's addressed these days with window functions, although
it's debatable how "convenient" they are, and in any event SQLite
doesn't support them.
In SQLite the hole is filled with ORDER BY ... LIMIT. The problem is
that construct is frequently misused, as in your example, and it is
unnecessarily complex.
How so? Observe that *order* is implicit in magnitude functions: max()
implies order without requiring the user to say so.
Much more powerful would be functions that return N values instead of
just one. The most convenient form would require support in the SQL
interpreter. For example:
select date, maxn(3, score) from scores group by by date
would produce (up to) three scores for each date.
Not only is the logic for that query awkward to express in SQL (any
version) but, because of its roundabout expression, it presents a
challenge to the query planner. With the notion of "top N" buried in
a function, the interpreter would be free to keep track of the top N
values without necessarily sorting them.
--jkl
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
