how to select the first N rows from a table-CodePudding

i am using postgresql. i want to be able to select or delete the first n rows after sorting the ascendingly according to the timestamp of insertion. i found an example but it uses a table named logtable, i do not have that logtable. in otherwords, i want to sort the records in the table based on the time of data insertion into the table then selected the first N rows

please let me know how to achieve this task

CodePudding user response：

You could use ROW_NUMBER:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY ts_insertion) rn
    FROM yourTable
)

SELECT *
FROM cte
WHERE rn <= N  -- replace N with your actual limit value
ORDER BY ts_insertion;

CodePudding user response：

If you only want the first n rows, you can use LIMIT n, a window function is not necessary.

You can also use OFFSET:

SELECT column1, column2
FROM table_name
ORDER BY column1 ASC
LIMIT row_count OFFSET row_to_skip;