Re: [sqlite] sqlite-users Digest, Vol 46, Issue 29
Pete p...@mollysrevenge.com wrote: Thanks. I guess I'd like to confirm just where column aliases can be referenced. I think they cannot be referenced within the list of column names in which they are defined, and they can be referenced in any other clauses of the SELECT statement, eg WHERE, ORDER BY, GROUP BY, HAVING. Is that correct? Per SQL standard, column aliases can be referenced in ORDER BY, GROUP BY and HAVING clauses. As an extension, SQLite also allows them in WHERE and JOIN ON clauses, but again, such usage is non-standard (though very convenient at times). Neither the standard nor SQLite implementation allow referencing aliases in the SELECT clause. -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 46, Issue 29
On 1 Nov 2011, at 12:26pm, Igor Tandetnik wrote: Per SQL standard, column aliases can be referenced in ORDER BY, GROUP BY and HAVING clauses. As an extension, SQLite also allows them in WHERE and JOIN ON clauses, but again, such usage is non-standard (though very convenient at times). Neither the standard nor SQLite implementation allow referencing aliases in the SELECT clause. Am I right that the most convenient way to do this might be by using a VIEW ? One could define a VIEW which had a number of columns like totalPrice = numItems * itemPrice then use this totalPrice column for things like sorting, right ? Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite-users Digest, Vol 46, Issue 29
Thanks. I guess I'd like to confirm just where column aliases can be referenced. I think they cannot be referenced within the list of column names in which they are defined, and they can be referenced in any other clauses of the SELECT statement, eg WHERE, ORDER BY, GROUP BY, HAVING. Is that correct? Pete -- Message: 11 Date: Fri, 28 Oct 2011 16:34:15 -0400 From: Igor Tandetnik itandet...@mvps.org To: sqlite-users@sqlite.org Subject: Re: [sqlite] Referring to column alias Message-ID: j8f3o3$mle$1...@dough.gmane.org Content-Type: text/plain; charset=UTF-8; format=flowed On 10/28/2011 4:28 PM, Pete wrote: I have another variation of this issue: SELECT col1 - col2 as Total, Total * price FROM tst ... gives an error no such column: Total. I can just repeat col1 - col2 of course, but wondering if there is a way to refer to Total within the SELECT. This is by design, blessed by SQL-92 standard. The closest you can get is something like SELECT Total, Total * price FROM (select col1 - col2 as Total, price from tst); This will likely be noticeably slower though. -- Igor Tandetnik -- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
