SQL tuple/lexicographic comparison with multiple directions-CodePudding

I need to return elements from a database query based on an inequality using the lexicographic ordering on multiple columns. As described in this question this is straightforward if I'm comparing all the columns in the same direction. Is there a straigtforward way to do this if I want to reverse the direction of the sort on some columns.

For instance, I might have columns A, B and C and values 5, 7, and 23 and I'd like to return something like:

WHERE A < 5 OR (A = 5 AND B > 7) OR (A = 5 AND B = 7 AND C < 23)

Is there any easier way to do this using tuples (I have to construct in a function without knowing the number of columns beforehand)? Note that, some columns are DateTime columns so I can't rely on tricks that apply only to integers (e.g. negating both sides). I'm happy to use postgresql specific tricks.

And, if not, is there a specific way/order I should build expressions like the above to best use multicolumn indexes?

CodePudding user response：

Just thinking if going the CTE route and creating a column which stores 0 or 1 for whether the data passes the specific filter criteria or not.

WITH CTE AS
  (
    SELECT 
      ..,
      ...,
      CASE 
        WHEN A < 5 THEN 1
        WHEN A = 5 AND B > 7 THEN 1
        WHEN A = 5 AND B = 7 AND C < 23 THEN 1
        ELSE 0
        END AS filter_criteria 
   )


SELECT
  ..,
  ..
FROM 
  CTE
WHERE filter_criteria = 1

OR, directly applying the CASE statement in the WHERE clause. This reduces the extra step of CTE

WHERE 1 = CASE 
            WHEN A < 5 THEN 1
            WHEN A = 5 AND B > 7 THEN 1
            WHEN A = 5 AND B = 7 AND C < 23 THEN 1
            ELSE 0
            END

CodePudding user response：

Referring to the thread you mentioned, can you try the idea WHERE (col_a, 'value_b') > ('value_a', col_b)