sqlite tuple comparison

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

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 the answer of ('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 a toy example, and that my actual application has tables with more columns and data types, and hundreds of million of rows.

If the solution is easily extensible to more/less columns, that's even better.

---

Tuple Comparison:

Tuples are ordered lexicographically, meaning that the sequences are ordered the same as their first differing elements. For example, (1,2,x) < (1,2,y) returns the same as x < y.

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

---

Here's the necessary SQL 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开发者_Go百科 values ('Bruce', 'Jackson', 1);
create unique index 'UNIQ' on mytable (First, Last, Score);

---

SQLite doesn't support tuple comparisons. But a row constructor is a kind of shorthand. You can get the same result with a more complicated WHERE clause. I've omitted the LIMIT 1 clause to make it easier to see that both queries return the same set. (On platforms that support row constructors, that is.)

This comparison

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

is equivalent to

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

And you can extend that 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 did not test this with NULLs in the data.

---

As of 2018, SQLite does support tuple comparison. The OP's query produces the expected output using the SQL statements provided. This way of writing the query also works. (I find between ... and ... more readable.)

SELECT First, Last, Score
FROM mytable
WHERE (First, Last, Score ) between ('John',  'Jordan',  5) and ('Mike',  'Taylor',  50) 
ORDER BY First, Last, Score
Limit 1

I don't know how long ago this was introduced.

---

I've been circumventing the lack of tuple comparison by using string concatenation (||) and a character sequence to ensure the fields don't "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 would 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 pretty costly and less terse but it works the same way and looks very similar.