Comparing sqlite tuple

Question

Trying to do the same thing as this question , but this time in sqlite. In my current application, I need to execute this type of request:

SELECT First, Last, Score FROM mytable WHERE ('John', 'Jordan', 5) <= (First, Last, Score ) AND (First, Last, Score) <= ('Mike', 'Taylor', 50) ORDER BY First, Last, Score LIMIT 1 

and get an answer ('Liz', 'Jordan', 2) , given this data:

 +-------+---------+-------+ | First | Last | Score | +-------+---------+-------+ | Liz | Jordan | 2 | | John | Jordan | 2 | | Liz | Lemon | 10 | | Mike | Taylor | 100 | | John | Jackson | 1000 | | Mike | Wayne | 1 | | Liz | Lemon | 20 | | Liz | Meyers | 5 | | Bruce | Jackson | 1 | +-------+---------+-------+ 

What is the most efficient way to accomplish this in sqlite? Please keep in mind that this is an example of toys, and that my actual application has tables with many columns and data types and hundreds of millions of rows.

If the solution is easily expandable to more / less columns, this is even better.

---

Comparison of tuples:

Tuples are ordered lexicographically, which means that the sequences are ordered in the same way as their first distinct elements. For example, (1,2, x) <(1,2, y) returns the same as x <y.

It is worth noting that SQL-92 (and mysql, oracle, postresql) implements this correctly. The standard uses a "string value constructor" to indicate what I call a tuple. The behavior is defined in excruciating detail in part 8.2.7, page 209 .

---

SQL is needed here to create the example:

 create table mytable ( First char(20), Last char(20), Score int ); insert into mytable values ('Liz', 'Jordan', 2); insert into mytable values ('John', 'Jordan', 2); insert into mytable values ('Liz', 'Lemon', 10); insert into mytable values ('Mike', 'Taylor', 100); insert into mytable values ('John', 'Jackson', 1000); insert into mytable values ('Mike', 'Wayne', 1); insert into mytable values ('Liz', 'Lemon', 20); insert into mytable values ('Liz', 'Meyers', 5); insert into mytable values ('Bruce', 'Jackson', 1); create unique index 'UNIQ' on mytable (First, Last, Score); 

Answer 1

SQLite does not support tuple mappings. But the string constructor is a kind of shorthand. You can get the same result with a more complex WHERE clause. I omitted the LIMIT 1 to make it easier to see that both queries return the same set. (On platforms that support string constructors, that is.)

This is a comparison.

 ROW(a,b) <= ROW(c,d) 

equivalently

 a < c OR (a = c AND b <= d) 

And you can expand this to as many columns as you need.

 SELECT First, Last, Score FROM mytable WHERE (('John' < First) OR ('John' = First AND 'Jordan' < Last) OR ('John' = First AND 'Jordan' = Last AND 5 <= Score)) AND ((First < 'Mike') OR (First = 'Mike' AND Last < 'Taylor') OR (First = 'Mike' AND Last = 'Taylor' AND Score <= 50)) ORDER BY First, Last, Score Liz Jordan 2 Liz Lemon 10 Liz Lemon 20 Liz Meyers 5 

I have not tested this with NULL in the data.

Answer 2

I circumvented the lack of tuple comparisons using string concatenation ( || ) and a sequence of characters so that the fields do not merge and cause incorrect matches ( - ).

 (First, Last, Score) <= ('Mike', 'Taylor', 50) 

becomes

 First||' - '||Last||' - '||Score <= 'Mike'||' - '||'Taylor'||' - '||'50' 

or

 First||' - '||Last||' - '||Score <= 'Mike - Taylor - 50' 

so your SELECT will be

 SELECT First, Last, Score FROM mytable WHERE 'John - Jordan - 5' <= First||' - '||Last||' - '||Score AND First||' - '||Last||' - '||Score <= 'Mike - Taylor - 50' ORDER BY First, Last, Score LIMIT 1 

String concatenation is quite expensive and less accurate, but works the same and looks very similar.