Drizzle TeamDT
Drizzle Team•8mo ago
Deen24ID

Issues from a WITH clause (CTE).

I have the following postgreSQL schema:

export const price = pgTable("price", {
  id: uuid().primaryKey().defaultRandom().notNull(),
  name: varchar({ length: 255 }).notNull(),
  buy: integer().notNull(),
  sell: integer().notNull(),
  lastUpdatedAt: timestamp({ withTimezone: true, mode: "string" })
    .defaultNow()
    .notNull(),
});


In the entries the same name can be repeated in many rows. For each name, I just want to keep the rows with the highest lastUpdatedAt, keeping other columns along. I can get these values using the following SQL query:

WITH priceLast as (SELECT name, MAX(price."lastUpdatedAt") as lastUpdatedAt FROM price GROUP BY name)
SELECT 
  price.id as id, 
  priceLast.name as name, 
  priceLast.lastUpdatedAt as lastUpdatedAt,
  price.buy as buy,
  price.sell as sell
FROM priceLast INNER JOIN price ON
priceLast.lastupdatedat = price."lastUpdatedAt" AND priceLast.name = price.name


But, I can't rewrite this in drizzle. Any help is greatly appreciated. Thanks!
Was this page helpful?