Hi! SQLDelight problem: I'm trying to use window f...

# squarelibraries

[Mateu]

Hi! SQLDelight problem:
I'm trying to use window functions but I'm facing compile dialect errors.
I've tried both sqllite (3-38-dialect) and postgresql dialects (I need it for local and server dbs) having different errors.

Using this simple sql:

CREATE TABLE highScore (
  id INTEGER NOT NULL,
  name TEXT NOT NULL,
  points INTEGER NOT NULL
);

mySelect:
SELECT
  name,
  RANK () OVER (
  ORDER BY points DESC
  ) rank
FROM highScore
;

With potsgresql I get:

Compiling with dialect app.cash.sqldelight.dialects.postgresql.PostgreSqlDialect

file.sq: (29, 15): ',', <compound operator real>, FOR, FROM, GROUP, HAVING, LIMIT, OFFSET, ORDER or WHERE expected, got '('
27    SELECT
28      name,
29      RANK () OVER (
                     ^
30      ORDER BY points DESC
31      ) rank
32    FROM highScore

With sqlite:

Failed to compile SqlCompoundSelectStmtImpl(COMPOUND_SELECT_STMT): [] :
SELECT
  name,
  row_number() OVER (ORDER BY points DESC) AS rank
FROM highScore

Is there something else that need to be done to support window functions?

[griffio]

🤔 the above syntax was only added to the HSQL grammar, so works with the sql queries ->https://github.com/cashapp/sqldelight/blob/1a2a6fcfc010ce207689d6b7b1fde47cca0c662[…]/main/kotlin/app/cash/sqldelight/dialects/hsql/grammar/hsql.bnf
That change needs to be added to the Postgresql and Sqlite grammar - I will add to your new issue as well

[Mateu]

Thanks!
Is there any other syntax equivalent that I can use?

[griffio]

🤔 I can only suggest something like

SELECT
  name,
  points,

 (SELECT COUNT(*)
        FROM highScore hs2
        WHERE hs2.points >= hs1.points) AS row_number
FROM highScore hs1
ORDER BY row_number ASC
;

Have play around and see if it matches your expected results 🧪

[Mateu]

Ok.
In Sqlite there is something defined (in fact the bugs are different from sqlite to postgresql): https://github.com/cashapp/sqldelight/blob/1a2a6fcfc010ce207689d6b7b1fde47cca0c662[…]lin/app/cash/sqldelight/dialects/sqlite_3_25/grammar/sqlite.bnf