What is the problem with this code in H2. It shows the modes (most frequent values) of V2 in every group defined by V1. In group 1 A appears twice and B appears twice so they are both modes. In group 2 A appears twice and B appears once so A is the mode. The select statement generates an error shown further down.
CREATE TABLE VV (V1 INT, V2 VARCHAR(1)); INSERT INTO VV VALUES (1, 'A'); INSERT INTO VV VALUES (1, 'A'); INSERT INTO VV VALUES (1, 'B'); INSERT INTO VV VALUES (1, 'B'); INSERT INTO VV VALUES (2, 'A'); INSERT INTO VV VALUES (2, 'A'); INSERT INTO VV VALUES (2, 'B'); SELECT V1, V2 FROM VV A GROUP BY V1, V2 HAVING COUNT(*) = (SELECT MAX(k) FROM (SELECT V1, V2, COUNT(*) AS K FROM VV B WHERE B.V1 = A.V1 GROUP BY V1, V2) S); The error message is this: Column "A.V1" not found; SQL statement: SELECT V1, V2 FROM VV A GROUP BY V1, V2 HAVING COUNT(*) = (SELECT MAX(k) FROM (SELECT V1, V2, COUNT(*) AS K FROM VV B WHERE B.V1 = A.V1 GROUP BY V1, V2) S) [42122-127] I also tried it in sqlite where it worked. C:\Program Files\H2\bin>sqlite3 SQLite version 3.6.18 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> sqlite> CREATE TABLE VV (V1 INT, V2 VARCHAR(1)); sqlite> INSERT INTO VV VALUES (1, 'A'); sqlite> INSERT INTO VV VALUES (1, 'A'); sqlite> INSERT INTO VV VALUES (1, 'B'); sqlite> INSERT INTO VV VALUES (1, 'B'); sqlite> INSERT INTO VV VALUES (2, 'A'); sqlite> INSERT INTO VV VALUES (2, 'A'); sqlite> INSERT INTO VV VALUES (2, 'B'); sqlite> sqlite> SELECT V1, V2 FROM VV A GROUP BY V1, V2 HAVING COUNT(*) = ...> (SELECT MAX(k) FROM ...> (SELECT V1, V2, COUNT(*) AS K FROM VV B WHERE B.v1 = A.v1 GROUP BY V1 , V2) S); 1|A 1|B 2|A -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/h2-database?hl=en.
