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
Hi @GStudiosX2 👋
You can use a combination of
orderBy (on id or createdAt, if exists in your table) and limit.
You can also use a prepared statement with placeholder if you want to reuse it for different limits (N)I need it to be ordered by asc(messages.created_at)
Ok, so use
asc instead of desc?
Yet, these are not the latest recordsWell 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 ?
you can order by multiple fields...
something like
db.select().from(table).orderBy(desc(table.id), asc(table.created_at));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
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
So do i add another column then ?
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
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
ask AI
id: serial("id").primaryKey()
This doesn't work it only picks the first one
Ok i think i got it working with subquerys
@GStudiosX2 what I often do is using a serial id with
autoincrement and a text public_id column where it’s neededEh i don't really want to mess too much with db stuff right now