Delete the oldest row

User Talk Base design
1

What sql query can I use to delete the first(oldest) row in my table? Thank you.

2

You can first select rows from the table and order by _ctime. Get the ID of the first row. Then delete the row by the ID.

1 Like
3

How do you get the id of the first row?

4

You can use SQL like below to get the oldest row and the _id:

SELECT _id, _ctime  FROM `table_name` order by _ctime limit 1
5

Thank you, I can get the row id now, but how can I combine into 1 query? I tried this:

delete from MyTable where _id = '(select _id from MyTable order by _ctime limit 1)'

I don’t get any error, but the row is not deleted.

6

This is not a SeaTable question.

A simple Google search should help you out. This, for example, might be an interesting resource: sql - Delete oldest records from database - Stack Overflow.

7

How is this not a SeaTable question? Are you saying I can’t ask for help with creating a query?
And I did in fact search for how to do this, but didn’t find anything that helped me.
The link that you shared above contains an example that is nearly identical to the query I posted above. But SeaTable requires single quotes around the _id value when querying by it, but I don’t know how to put in the quotes when doing the sub-query, so that’s what I was asking for help with.

8

You can ask any question you want. But a question re. SQL is “not a SeaTable question” and this forum is not the best place for asking SQL related questions. Chances are good that you find an answer elsewhere.

Case in point for my statement: “… this forum is not the best place for SQL related questions.”

_id values are just strings. No special treatment needed.

I usually cast the entire sql statement in double quotes. Then you can use single quotes within the statement if you want. Note that column names with spaces must be put in backticks (see Daniel’s comment above and this link.)

Please consult these two resources:

9

SeaTable SQL interface does not support sub-query yet. So you have to write it as two statements with manually paste the row id from the first statement. Or you need to write a Python script to call SQL for automation.

10

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.