Folks, I must chime in here. :)
Let me quote from the SQL:2011 standard:
7.10 <having clause>
Syntax Rules
1) Let HC be the <having clause>. Let TE be the <table expression>
that immediately contains HC. If TE
does not immediately contain a <group by clause>, then “GROUP BY
()” is implicit.
There is no doubt about the fact that HAVING without GROUP BY is
perfectly fine in the SQL standard and in fact, almost all 21
databases for which we run integration tests (apart from SQLite) do
support this.
What's clearly wrong, though, is the OP's usage of HAVING:
SELECT account_id, time FROM TABLE_TEST HAVING account_id=10;
Since GROUP BY () is implicit, account_id (and any other non-aggregate
expression, or non-outer reference) must not appear in the HAVING clause.
Hope this helps,
Lukas
Am Freitag, 23. Oktober 2015 17:33:05 UTC+2 schrieb Thomas Mueller:
Hi,
> supported by major rdbms
No. It only "works" with MySQL, and there, I guess it's just for
backward compatibility (I didn't test with ANSI mode). The test
also fails with Oracle and MS SQL Server (using SQLFiddle). So the
MS SQL Server documentation is actually wrong there.
Regards,
Thomas
On Fri, Oct 23, 2015 at 3:51 PM, Taras Fedkiv <[email protected]>
wrote:
Rami,
According to
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
<http://www.contrib.andrew.cmu.edu/%7Eshadow/sql/sql1992.txt>
(sec 7.8)
HAVING filters results of the preceding <from clause>, <where
clause>, or <group by clause>. If that clause is not a <group
by clause>, then result consists of a single group and does
not have a grouping column.
Its long story to explain my case. In general API returns data
based on incoming SELECT statement. On final stage data is
inserted into h2 and passed query without <where clause> is
executed on it.
Anyway - its a nice feature that is supported by major rdbms
and is specified in standard. We can always find some dbs that
do not support this or that behaviour, but the main rule is
standard.
Thank you,
Taras
On Thursday, October 22, 2015 at 2:46:47 PM UTC+3, Rami Ojares
wrote:
AFAIK having is a feature that allows the user to apply to
results of group by.
The order of sql statement is as follows:
- from
- where
- group by
- having
- select clause
- order by
If I am correct my question to Taras is what do you mean
when you want to use having without group by?
Can't you just use where clause?
- Rami
On 22.10.2015 13:44, Taras Fedkiv wrote:
Thomas,
Please describe in which cases HAVING without GROUP BY is
allowed. Current documentation has no information about it.
Unfortunately you didn't mention Oracle, MySQL, MS that
support this feature and are the most popular rdbms. SQL
92 allows it, too.
Thank you,
Taras
On Wednesday, October 21, 2015 at 6:52:13 PM UTC+3,
Thomas Mueller wrote:
Hi,
Your example works with H2 (unfortunately), but this
one fails (it throws an exception):
drop table table_test;
CREATE TABLE TABLE_TEST(account_id BIGINT, time BIGINT);
INSERT INTO TABLE_TEST (account_id,time) VALUES (10,2);
INSERT INTO TABLE_TEST (account_id,time) VALUES (10,3);
SELECT account_id, time FROM TABLE_TEST HAVING
account_id=10;
It also fails with PostgreSQL, and SQLite, and Apache
Derby, and HSQLDB. So: nope, this will not be
supported in H2.
Regards,
Thomas
On Wednesday, October 21, 2015, Taras Fedkiv
<[email protected]> wrote:
Hi,
H2(v1.4.190) throws error if HAVING is used
without GROUP BY.
Ex:
CREATE MEMORY TABLE IF NOT EXISTS
TABLE_TEST(account_id BIGINT, time BIGINT) NOT
PERSISTENT
INSERT INTO TABLE_TEST (account_id,time) VALUES
(10,2)
SELECT account_id, time FROM TABLE_TEST HAVING
account_id=10 //throws
org.h2.jdbc.JdbcSQLException: Column
"""account_id""" must be in the GROUP BY list;
Please add support of HAVING without GROUP BY.
MySQL, PostgreSQL support this feature.
Thank you,
Taras
The information in this message may be
confidential. It is intended solely for
the addressee(s). If you are not the intended
recipient, any disclosure,
copying or distribution of the message, or any
action or omission taken by you
in reliance on it, is prohibited and may be
unlawful. Please immediately
contact the sender if you have received this
message in error.
--
You received this message because you are
subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving
emails from it, send an email to
[email protected].
To post to this group, send email to
[email protected].
Visit this group at
http://groups.google.com/group/h2-database
<http://groups.google.com/group/h2-database>.
For more options, visit
https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
The information in this message may be confidential. It
is intended solely for
the addressee(s). If you are not the intended recipient,
any disclosure,
copying or distribution of the message, or any action or
omission taken by you
in reliance on it, is prohibited and may be unlawful.
Please immediately
contact the sender if you have received this message in
error.
--
You received this message because you are subscribed to
the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails
from it, send an email to [email protected].
To post to this group, send email to
[email protected].
Visit this group at
http://groups.google.com/group/h2-database
<http://groups.google.com/group/h2-database>.
For more options, visit
https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
The information in this message may be confidential. It is
intended solely for
the addressee(s). If you are not the intended recipient, any
disclosure,
copying or distribution of the message, or any action or
omission taken by you
in reliance on it, is prohibited and may be unlawful. Please
immediately
contact the sender if you have received this message in error.
--
You received this message because you are subscribed to the
Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from
it, send an email to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/h2-database
<http://groups.google.com/group/h2-database>.
For more options, visit https://groups.google.com/d/optout
<https://groups.google.com/d/optout>.
--
You received this message because you are subscribed to the Google
Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send
an email to [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
