Tuple comparison with ORACLE and SQLSERVER

Question

I am working on cursor based pagination using multiple columns. I need to make a tuple comparison since date might not be a unique value.

When I am using MySQL and PostgreSQL, I can do it like this.

WHERE (date, id) < (:date, :id) ORDER BY date DESC, id DESC

I can't do the same with Oracle and SQL Server. I am wondering how can I do a tuple comparison with those databases.

Answer 1

Assuming that this MySQL documentation documents the behavior you want

For row comparisons, (a, b) <= (x, y) is equivalent to:

(a < x) OR ((a = x) AND (b <= y))

Then you should be able to write the query across all four platforms as

WHERE date < :date
   OR ((date = :date) and (id < :id)) 
ORDER BY date DESC, id DESC