Syntax error in SQL statement "SELECT ..."; expected "identifier"

[etyr...@gmail.com]

Hi All,

I have a table, policies, that has "id" and "generationId" columns.  I want to get a row for "id" with the greatest "generationId" for that "id".  My query is as follows:

SELECT * FROM policies JOIN WITH (SELECT id, MAX(generationId) AS maxgen FROM policies GROUP BY id) AS mg ON policies.id = mg.id AND policies.generationId = mg.maxgen

It seems that the database doesn't recognize "maxgen" as an identifier. Below is the error I get.  Any suggestions would be appreciated.  I am using 1.4.188 beta.

Syntax error in SQL statement "SELECT * FROM POLICIES JOIN WITH[*] (SELECT ID, MAX(GENERATIONID) AS MAXGEN FROM POLICIES GROUP BY ID) AS MG ON POLICIES.ID = MG.ID AND POLICIES.GENERATIONID = MG.MAXGEN "; expected "identifier"; SQL statement:
SELECT * FROM policies JOIN WITH (SELECT id, MAX(generationId) AS maxgen FROM policies GROUP BY id) AS mg ON policies.id = mg.id AND policies.generationId = mg.maxgen [42001-188] 42001/42001 (Help)
org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT * FROM POLICIES JOIN WITH[*] (SELECT ID, MAX(GENERATIONID) AS MAXGEN FROM POLICIES GROUP BY ID) AS MG ON POLICIES.ID = MG.ID AND POLICIES.GENERATIONID = MG.MAXGEN "; expected "identifier"; SQL statement:
SELECT * FROM policies JOIN WITH (SELECT id, MAX(generationId) AS maxgen FROM policies GROUP BY id) AS mg ON policies.id = mg.id AND policies.generationId = mg.maxgen [42001-188]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.getSyntaxError(DbException.java:205)
    at org.h2.command.Parser.readIdentifierWithSchema(Parser.java:3060)
    at org.h2.command.Parser.readTableFilter(Parser.java:1191)
    at org.h2.command.Parser.readJoin(Parser.java:1540)
    at org.h2.command.Parser.parseJoinTableFilter(Parser.java:1879)
    at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1874)
    at org.h2.command.Parser.parseSelectSimple(Parser.java:1982)
    at org.h2.command.Parser.parseSelectSub(Parser.java:1867)
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1688)
    at org.h2.command.Parser.parseSelect(Parser.java:1676)
    at org.h2.command.Parser.parsePrepared(Parser.java:432)
    at org.h2.command.Parser.parse(Parser.java:304)
    at org.h2.command.Parser.parse(Parser.java:276)
    at org.h2.command.Parser.prepareCommand(Parser.java:241)
    at org.h2.engine.Session.prepareLocal(Session.java:461)
    at org.h2.server.TcpServerThread.process(TcpServerThread.java:264)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:159)
    at java.lang.Thread.run(Thread.java:745)

    at org.h2.engine.SessionRemote.done(SessionRemote.java:624)
    at org.h2.command.CommandRemote.prepare(CommandRemote.java:68)
    at org.h2.command.CommandRemote.<init>(CommandRemote.java:45)
    at org.h2.engine.SessionRemote.prepareCommand(SessionRemote.java:494)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188)
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:170)
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:158)
    at org.h2.server.web.WebApp.getResult(WebApp.java:1388)
    at org.h2.server.web.WebApp.query(WebApp.java:1061)
    at org.h2.server.web.WebApp$1.next(WebApp.java:1023)
    at org.h2.server.web.WebApp$1.next(WebApp.java:1010)
    at org.h2.server.web.WebThread.process(WebThread.java:164)
    at org.h2.server.web.WebThread.run(WebThread.java:89)
    at java.lang.Thread.run(Thread.java:745)

[Noel Grandin]

We don't support JOIN WITH

[Steve McLeod]

You'll notice that immediately after the WITH keyword, there is [*]. This indicates where the syntax error was.

Without the WITH it should work as intended, no?

SELECT * 

  FROM policies 

  JOIN

     (SELECT id, MAX(generationId) AS maxgen FROM policies GROUP BY id) AS mg 

  ON policies.id = mg.id AND policies.generationId = mg.maxgen

[etyr...@gmail.com]

Taking out WITH works.  Thanks for your help!