CODEWITHSUNDEEP
sqloracle-database
David
David

Reputation: 10758

get next row after specific row in oracle

I'm trying to figure out a way to reliably get the next row after another. I have the following schema:

id varchar(36),
received_at timestamp(6) with time zone

This query works most of the time

SELECT *
FROM receipt_emails
WHERE id > '50e8340a-2db5-42c2-add8-81ba732e9123'
ORDER BY RECEIVED_AT ASC
FETCH NEXT 1 ROWS ONLY;

But sometimes it doesn't and I don't know why. I have the following 2 rows in the database now. 

id                                   | received_at
50e8340a-2db5-42c2-add8-81ba732e9123 | "2015-08-04 05:16:13.000000"
4744eb81-9233-462a-8ee6-43302ef85671 | "2015-08-04 05:17:53.000000"

Executing the above query returns zero results. I would expect to get the row with the id of 4744eb81-9233-462a-8ee6-43302ef85671. I have a feeling it has to do with the where portion of the query not doing what I'm intending.

Upvotes: 1

Views: 9209

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

I think this is what you want:

SELECT re.*
FROM receipt_emails re
WHERE received_at > (SELECT received_at
                     FROM receipt_emails re2
                     WHERE re2.id = '50e8340a-2db5-42c2-add8-81ba732e9123'
                    )
ORDER BY RECEIVED_AT ASC
FETCH NEXT 1 ROWS ONLY;

The comparison should be to the timestamp, not the id.

Upvotes: 2

Related Questions