How to work with window function ROW_NUMBER correctly?
How to type correctly something like this? I struggle with the ROW_NUMBER...
const query = `
WITH ranked_licenses AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY type ORDER BY l."expiresAt" ASC
) as rn
FROM license l
WHERE l.active = ${String(true)}
)
SELECT * FROM ranked_licenses WHERE rn = 1;
`;
const query = `
WITH ranked_licenses AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY type ORDER BY l."expiresAt" ASC
) as rn
FROM license l
WHERE l.active = ${String(true)}
)
SELECT * FROM ranked_licenses WHERE rn = 1;
`;
3 Replies
ok, I've done it myself. hopefully that is the way how it should be done:
const query = kysely
.with('ranked_licenses', (kysely) =>
kysely
.selectFrom('license')
.selectAll()
.select((eb) =>
sql<number>`ROW_NUMBER() OVER (
PARTITION BY ${eb.ref('type')} ORDER BY ${eb.ref(
'expiresAt',
)} ASC
)`.as('rn'),
)
.where('active', '=', true)
)
.selectFrom('ranked_licenses')
.selectAll()
.where('rn', '=', 1)
const query = kysely
.with('ranked_licenses', (kysely) =>
kysely
.selectFrom('license')
.selectAll()
.select((eb) =>
sql<number>`ROW_NUMBER() OVER (
PARTITION BY ${eb.ref('type')} ORDER BY ${eb.ref(
'expiresAt',
)} ASC
)`.as('rn'),
)
.where('active', '=', true)
)
.selectFrom('ranked_licenses')
.selectAll()
.where('rn', '=', 1)
Hey 👋🏻
There's a built-in way to achieve all of this.
https://kyse.link/?p=s&i=IDSJEyIpghdtWnQpO1t0
const query = kysely
.with("ranked_licenses", (qc) =>
qc
.selectFrom("license")
.where("active", "=", true)
.selectAll()
.select((eb) =>
eb.fn
.agg<number>("row_number", []) // <----- if we don't have built-in aggregate function you need, you can use "agg" to be able to use our window function methods afterwards.
.over((ob) => ob.partitionBy("type").orderBy("expiresAt", "asc"))
.as("rn"),
),
)
.selectFrom("ranked_licenses")
.where("rn", "=", 1)
.selectAll()
const query = kysely
.with("ranked_licenses", (qc) =>
qc
.selectFrom("license")
.where("active", "=", true)
.selectAll()
.select((eb) =>
eb.fn
.agg<number>("row_number", []) // <----- if we don't have built-in aggregate function you need, you can use "agg" to be able to use our window function methods afterwards.
.over((ob) => ob.partitionBy("type").orderBy("expiresAt", "asc"))
.as("rn"),
),
)
.selectFrom("ranked_licenses")
.where("rn", "=", 1)
.selectAll()
cool. thanks