SQL comparison matching ORDER BY

Question

When one orders results by multiple columns the database has to do something to compare NULL values (e.g. order them first last). But how can I replicate that same order in a comparison in my query (i.e. I need to compare row A before row B just when A would occur before B in the, fixed, ORDER BY).

For instance, how could one find all rows that occur before row R= (date: 2020-01-01, x: NULL, y: 20) in the ordering ( date DESC, x ASC, y DESC)? In other words, the query should return the set of elements that would appear before R appears in the query "SELECT T.* ORDER BY (T.date DESC, T.x ASC, T.y DESC)

(I mean without expanding out every possible case with IS NULL and IS NOT NULLS) I'm happy to use postgresql specific features if necessary.

Answer 1

I think you are confusing 2 different concepts and then wondering why things don't work in the way you expect. But continuing this discussion is probably unproductive - it works the way it does and you have to live with it. You have to options for dealing with comparisons when your data contains nulls e.g:

WHERE (a.col1 = b.col1 OR (a.col1 IS NULL AND b.col1 IS NULL))

WHERE nvl(a.col1,0) = nvl(b.col1,0)
-- you might need to replace the 0 in the NVL statements with a value that works with you particular data