how to select the first N rows from a table

Question

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

Hi, it would help if you could add the table structure, a small data sample and the expected result. Preferably within a fiddle. — Jim Jones, Jul 13 '22 at 14:48

Tim Biegeleisen · Answer 1 · 2022-07-13T12:48:48.727

1

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;

edited Jul 13 '22 at 12:48

answered Jul 13 '22 at 12:44

Tim Biegeleisen

502,043
27
286
360

thnaks, but would it be sorted ascendingly in the way you provided? – Amrmsmb Jul 13 '22 at 12:46
Then add an `ORDER BY` clause to the outer query. – Tim Biegeleisen Jul 13 '22 at 12:49
what about ts_insertion? i do not have it – Amrmsmb Jul 13 '22 at 12:50
please, i want to sort the entried according to the time of insertion ascendingly – Amrmsmb Jul 13 '22 at 13:10
Just replace `ts_insertation` with your column name. – Belayer Jul 13 '22 at 17:54

score 0 · Answer 2 · edited Jul 13 '22 at 13:24

0

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;

edited Jul 13 '22 at 13:24

marc_s

732,580
175
1,330
1,459

answered Jul 13 '22 at 12:50

nbk

45,398
8
30
47

how to select the first N rows from a table

2 Answers2

Linked