Drizzle ORM how to use subquery inside WHERE

suppose you have the following SQLite data , in a table named "A"

id  name    last_updated
1   Apple   100
2   Banana  100
3   Apple   200
4   Banana  200
5   Carrot  200
6   Banana  300
7   Apple   300

you want to get the entry for each name that corresponds to the biggest last_updated, given last_updated is <= to a certain parameter value, for example given last_updated <= 250 the result should be:

| id  | name   | last_updated |
| --- | ------ | ------------ |
| 3   | Apple  | 200          |
| 4   | Banana | 200          |
| 5   | Carrot | 200          |

In Raw SQL, the setup will be:

-- INIT database
CREATE TABLE IF NOT EXISTS A (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    last_updated INTEGER NOT NULL
);
INSERT INTO A (name, last_updated) VALUES ('Apple', 100);
INSERT INTO A (name, last_updated) VALUES ('Banana', 100);
INSERT INTO A (name, last_updated) VALUES ('Apple', 200);
INSERT INTO A (name, last_updated) VALUES ('Banana', 200);
INSERT INTO A (name, last_updated) VALUES ('Carrot', 200);
INSERT INTO A (name, last_updated) VALUES ('Banana', 300);
INSERT INTO A (name, last_updated) VALUES ('Apple', 300);

How to write a Drizzle Query to achieve this?

So in Raw SQL, I can achieve this via subquery inside WHERE clause:

-- QUERY database
SELECT a.id, a.name, a.last_updated
FROM A AS a
WHERE a.last_updated = (
    SELECT MAX(last_updated)
    FROM A
    WHERE name = a.name
    AND last_updated <= 250
)
AND a.last_updated <= 250;

I tried so many variations in Drizzle ORM, but it seems I just can't get it right. My latest attempt looks something like this:

const dayLuxonToBigint = 250

return await db
  .select()
  .from(A)
  .where(
    and(
      lte(a.last_updated, dayLuxonToBigint),
      sql`SELECT max(${A.last_updated}) FROM ${A}
WHERE id = a.id
AND ${A.last_updated} <= ${dayLuxonToBigint}`,
    ),
  );

I hope somebody can help, thanks!

You can declare your subquery separately. In order to use MAX aggregation - you need to group by the name.

const subQuery = db.select({
   name: A.name,
   max_last_updated: sql<number>`cast(max(${A.last_updated}) as int)`.as('max_last_updated')
 })
  .from(A)
  .where(lte(a.last_updated, dayLuxonToBigint))
  .groupBy(A.name)
  .as('subQuery');

await db.select()
  .from(A)
  .innerJoin(subQuery, and(
     eq(A.name, subQuery.name),
     eq(A.last_updated, subQuery.max_last_updated)
   ))

You can read about the proposed drizzle approach in their documentation here