SQLITE tuple comparison - comparison, Group, SQLITE, Yuan

Try to do the same thing as this question, but this time in sqlite. In my current application, I need to be able to execute 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

Get these data, get the answer of (‘Liz’,’Jordan’,2):

+-------+------- --+-------+
| 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 |
+-------+---------+-------+

The most effective way to achieve this goal in SQLite What is the method? Keep in mind that this is a toy example, my actual application has tables with more columns and data types and hundreds of millions of rows.

If the solution can be easily extended to more/less Column, that’s even better.

Tuple comparison:

Tuples are sorted lexicographically, which means that the sequence is sorted in the same order as their first different element For example, (1,2,x)<(1,2,y) returns the same value as x

This is necessary to create the example SQL:

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);

SQLite does not support tuple comparison. But the row constructor is a shorthand. You can use a more complex WHERE clause to get the same result. I omitted the LIMIT 1 clause to make it easier to see Both queries return the same set. (On platforms that support row constructors, ie.)

This comparison

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

Equivalent to

a 
You can expand it to as many columns as you need.

SELECT First, Last, Score
FROM mytable
WHERE
 (('John'  ('John' = First AND'Jordan'  ('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 it with NULL in the data.
As of 2018, SQLite does support tuple comparison. OP's query uses the provided SQL statement to generate expectations Output. This way of writing queries is also effective. (I find that between... and... is more readable.)

SELECT First, Last, Score
FROM mytable
WHERE (First, Last, Score) between ('John','Jor dan', 5) and ('Mike','Taylor', 50) 
ORDER BY First, Last, Score
Limit 1
I don’t know how long ago this was introduced .

Try to do the same thing as this question, but this time in sqlite. In my current application, I need to be able to perform such queries: < p>

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< /pre>

Get these data and get the answer of ('Liz','Jordan',2):

+-------+-- -------+-------+
| 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 |
+-------+---------+-------+

Implement this in sqlite What is the most effective way to achieve a goal? Keep in mind that this is a toy example, my actual application has tables with more columns and data types and hundreds of millions of rows.