SQL: tuple comparison

Just do:

SELECT colA     , colB     , colCFROM mytableWHERE ( ('A',  'B',  'C') <= (colA, colB, colC ) )  AND ( (colA, colB, colC) <= ('D',  'E',  'F' ) )ORDER BY colA, colB, colCLIMIT 1;

It works just fine. And I suspect is should be pretty fast, too.

This is equivalent but it may have better performance, depending on your tables:

SELECT m.colA     , m.colB     , m.colCFROM mytable mWHERE ( ('A',  'B',  'C') <= (m.colA, m.colB, m.colC) )  AND ( (m.colA, m.colB, m.colC) <= ('D',  'E',  'F') )  AND NOT EXISTS  ( SELECT 1    FROM mytable b    WHERE (b.colA, b.colB, b.colC) < (m. colA, m.colB, m.colC)      AND ( ('A',  'B',  'C') <= (b.colA, b.colB, b.colC) )  );

---EDIT---: (Previous wrong trials removed)

2nd try (not really relational algebra).

This works but only when the fields are char(1):

SELECT colA, colB, colCFROM mytableWHERE CONCAT(colA, colB, colC)      BETWEEN CONCAT('A', 'B', 'C')      AND CONCAT('D', 'E', 'F')ORDER BY colA, colB, colCLIMIT 1 ; 

I thought that a view that shows all combinations of tuples from mytable that are less than or equal to tuples of the same table might be helpful, as it can be used for other comparisons:

CREATE VIEW lessORequal AS( SELECT a.colA AS smallA       , a.colB AS smallB       , a.colC AS smallC       , b.colA AS largeA       , b.colB AS largeB       , b.colC AS largeC  FROM mytable a    JOIN mytable b      ON (a.colA < b.colA)         OR ( (a.colA = b.colA)               AND ( (a.colB < b.colB)                     OR (a.colB = b.colB                        AND a.colC <= b.colC)                   )            )  ) ;

Using similar technique, this solves the question. It works with any kind of fields (int, float, char of any length). It's going to be kind of awkard and complicated though if one tries to add more fields.

SELECT colA, colB, colCFROM mytable mWHERE ( ('A' < colA)        OR ( ('A' = colA)              AND ( ('B' < colB)                    OR ('B' = colB                       AND 'C' <= colC)                  )           )      )  AND ( (colA < 'D')         OR ( (colA = 'D')              AND ( (colB < 'E')                    OR (colB = 'E'                       AND colC <= 'F')                  )            )      )ORDER BY colA, colB, colCLIMIT 1 ; 

One also define a function:

CREATE FUNCTION IslessORequalThan( lowA CHAR(1)                                 , lowB CHAR(1)                                 , lowC CHAR(1)                                 , highA CHAR(1)                                 , highB CHAR(1)                                 , highC CHAR(1)                                 )RETURNS booleanRETURN ( (lowA < highA)         OR ( (lowA = highA)               AND ( (lowB < highB)                     OR ( (lowB = highB)                          AND (lowC <= highC)                        )                   )            )       );

and use it to solve the same or similar problems. This solves the question again. The query is elegant but a new function has to be created if the type or number of fields is changed.

SELECT colA     , colB     , colCFROM mytable WHERE IslessORequalThan(  'A',  'B',  'C', colA, colB, colC )  AND IslessORequalThan( colA, colB, colC,  'D',  'E',  'F' )ORDER BY colA, colB, colCLIMIT 1;

Until then, and because the condition

(colA, colB, colC) BETWEEN ('A', 'B', 'C') AND ('D', 'E', 'F')

was not allowed in MySQL, I thought that

('A', 'B', 'C') <= (colA, colB, colC)

was not allowed as well. But I was wrong.