DT
Drizzle Team•9mo ago
danfascia

Selecting a random record (libSQL SQLite)

I have a table of questions and I would like to select a single random record to create a quiz. In raw SQL I would use 
SELECT * FROM questions ORDER BY RANDOM() LIMIT(1)
SELECT * FROM questions ORDER BY RANDOM() LIMIT(1)
I have no idea how to model this into a drizzle query as there only seem to be asc and desc methods on orderBy - I am using libSQL Thanks
2 Replies
Mykhailo
Mykhailo•9mo ago
Hey @danfascia! You can try this: 
import { products } from '../drizzle/schema';
import { sql } from 'drizzle-orm';

await db
    .select()
    .from(products)
    .orderBy(sql`RANDOM()`)
    .limit(1);
import { products } from '../drizzle/schema';
import { sql } from 'drizzle-orm';

await db
    .select()
    .from(products)
    .orderBy(sql`RANDOM()`)
    .limit(1);
You can read more about sql operator in the docs.
danfascia
danfasciaOP•9mo ago
Thank you that's perfect and it works! Great tip

Did you find this page helpful?