Get N of last rows

Hey, is there any way to get a list of N last rows of a table ? If you need the query just ask please ping

14 Replies

n00ki•2mo ago

Hi @GStudiosX2 👋 You can use a combination of orderBy (on id or createdAt, if exists in your table) and limit.

const result = await db
    .select()
    .from(yourTable)
    .orderBy(desc(yourTable.id))  // Assuming 'id' is your primary key or timestamp field
    .limit(n);

const result = await db
    .select()
    .from(yourTable)
    .orderBy(desc(yourTable.id))  // Assuming 'id' is your primary key or timestamp field
    .limit(n);

You can also use a prepared statement with placeholder if you want to reuse it for different limits (N)

GStudiosX2•2mo ago

I need it to be ordered by asc(messages.created_at)

n00ki•2mo ago

Ok, so use asc instead of desc? Yet, these are not the latest records

GStudiosX2•2mo ago

Well i haven't checked if it returns the latest records or not i just assumed it wouldn't If i use desc can i just reverse it so the older messages show before new ones from the last 30 ?

n00ki•2mo ago

you can order by multiple fields... something like db.select().from(table).orderBy(desc(table.id), asc(table.created_at));

GStudiosX2•2mo ago

I would need to make the id a number for that because rn its a text I might just do desc(table.created_at) and then just reverse it since it seems to work but I'll just have to probably add a bunch of rows to test

nk•2mo ago

I would avoid doing that If you have an ID string don't use it like that Just have a number as well And sorting by createdAt is slower I always use the id

GStudiosX2•2mo ago

So do i add another column then ?

nk•2mo ago

I would put an index on id/created_at desc so it's fast I would add back the serial id I only use createdAt for display values not for sorting

GStudiosX2•2mo ago

How would i do that? Do i just change the id to serial ? Sorry for the ping but how do i do this ? I'm new to drizzle and don't know much SQL

nk•2mo ago

ask AI id: serial("id").primaryKey()

GStudiosX2•2mo ago

This doesn't work it only picks the first one Ok i think i got it working with subquerys

n00ki•2mo ago

@GStudiosX2 what I often do is using a serial id with autoincrement and a text public_id column where it’s needed

GStudiosX2•2mo ago

Eh i don't really want to mess too much with db stuff right now

Gaming

Programming

Get N of last rows