select all rows after specific uuid in postgresql

Question

I want to select all rows starting after a specific uuid, for example here is the data

+--------------------------------------+-----------+------------+
|                 uuid                 | divisi_id | divisionid |
+--------------------------------------+-----------+------------+
| b303a96b-2a03-4b5e-90a1-6b3631fc82af | BTT       |          3 |
| 8c4bf1b8-7477-42e4-affb-31bafa8648f1 | BTT       |          3 |
| 6639909d-74e1-4dec-a1f3-f70703c0b6c6 | BTT       |          3 |
| 1a1aa367-1467-4811-848d-694dbe98a5a8 | BTT       |          3 |
| e739b352-d952-4ec8-980e-a50180e18144 | BTT       |          3 |
+--------------------------------------+-----------+------------+

now, for example I have last uuid = 8c4bf1b8-7477-42e4-affb-31bafa8648f1 (the 2nd entry). how to select rows after that uuid? i have no idea about it.

Please read meta.stackoverflow.com/questions/285551/… and the accepted answer — user330315
– user330315, Commented Apr 27, 2017 at 6:11
uuids have no natural ordering so there is no such thing as the uuid "after" another one — user330315
– user330315, Commented Apr 27, 2017 at 6:12
thank you @a_horse_with_no_name. I will convert the image to a text table. and so, there is no solution for my issue? — Dariel Pratama
– Dariel Pratama, Commented Apr 27, 2017 at 6:14
please first edit your question to formatted text and define the sorting algorythm — Vao Tsun
– Vao Tsun, Commented Apr 27, 2017 at 7:32
uuids have an ordering, but it is rather a technical ordering (and certainly have nothing to do with f.ex. "insertion order") -- your question's "after" implies that you want some row ordering. But in PostgreSQL, there is no default ordering (in fact, all other RDBMS also lack that, some just have a de facto ordering, which still shouldn't be relied upon). In short: if you have something to ORDER BY upon, your task is trivial, if you have no such thing, you cannot solve that at all. — pozs
– pozs, Commented Apr 27, 2017 at 7:59

Anurag Dadheech · Accepted Answer · 2017-04-27 08:17:49Z

2

Try this

SELECT d1.* FROM
(SELECT Row_Number() over (order by id) AS RowIndex, * from tableName ) AS d1 
INNER JOIN 
(SELECT Row_Number() over (order by id) AS RowIndex, * from tableName) AS d2 
ON (d2.uuid  = '8c4bf1b8-7477-42e4-affb-31bafa8648f1' and d1.RowIndex > d2.RowIndex)

Hope you have primary key!!

If you don't have Primary key than

SELECT d1.* FROM
(SELECT Row_Number() over (order by (select null)) AS RowIndex, * from tableName) AS d1 
INNER JOIN 
(SELECT Row_Number() over (order by (select null)) AS RowIndex, * from tableName) AS d2 
ON (d2.uuid  = '8c4bf1b8-7477-42e4-affb-31bafa8648f1' and d1.RowIndex > d2.RowIndex)

edited Apr 27, 2017 at 8:17

answered Apr 27, 2017 at 7:01

Anurag Dadheech

6291 gold badge5 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vao Tsun Over a year ago

please ask OP if he has an id UK, otherwise your answer won't work

jwanglof Over a year ago

This helped me to implement token based pagination, thanks a lot!

Collectives™ on Stack Overflow

select all rows after specific uuid in postgresql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related