Thomas Steffen <[EMAIL PROTECTED]> writes:
> On 4/14/05, [EMAIL PROTECTED]
> <[EMAIL PROTECTED]> wrote:
>> How about these:
>>
>> SELECT * from Mactor WHERE id = (SELECT MAX(id) FROM Mactor);
>> SELECT * from Mactor WHERE id = (SELECT MIN(id) FROM Mactor);
>
> I am working on a similar problem at the moment, but unless I missed
> something, ORDER BY id LIMIT 1 works fine for me. Is there any
> benefit of one formulation against the other? Is the nested SELECT
> less efficient? Or are they identical in bytecode?
EXPLAIN is your friend. As can be seen by EXPLAINing each query (see below),
there are fewer instructions involved in the one with the subquery, and no
sorts or loops as are done in the initial method. (Note that I've changed the
maximum value to what fits in a signed 32-bit field since I'm doing this with
2.8.16. You could try a similar experiment with 3.0.x. Actually, it looks
like you don't even need the WHERE clause in the original query, and I've
tested that modification at the end of the EXPLAINation below, as well.
> What if id is not unique, and I may have rows with identical id?
The original poster had 'id' as INTEGER PRIMARY KEY so that wouldn't be
possible. If it is possible, then you'd have to decide what you wanted to do
with multiple results.
% sqlite :memory:
SQLite version 2.8.16
Enter ".help" for instructions
sqlite> CREATE TABLE Mactor
...> (
...> id INTEGER PRIMARY KEY,
...> name TEXT,
...> -- any other fields
...> comment TEXT
...> );
sqlite> explain SELECT * FROM Mactor WHERE id < 2147483647 ORDER BY id DESC
LIMIT 1;
addr|opcode|p1|p2|p3
0|ColumnName|0|0|id
1|ColumnName|1|0|name
2|ColumnName|2|1|comment
3|Integer|-1|0|
4|MemStore|0|1|
5|ColumnName|3|0|INTEGER
6|ColumnName|4|0|TEXT
7|ColumnName|5|0|TEXT
8|Integer|0|0|
9|OpenRead|0|3|Mactor
10|VerifyCookie|0|31|
11|Rewind|0|25|
12|Integer|2147483647|0|2147483647
13|MemStore|1|1|
14|Recno|0|0|
15|MemLoad|1|0|
16|Ge|0|25|
17|Recno|0|0|
18|Column|0|1|
19|Column|0|2|
20|SortMakeRec|3|0|
21|Recno|0|0|
22|SortMakeKey|1|0|-
23|SortPut|0|0|
24|Next|0|14|
25|Close|0|0|
26|Sort|0|0|
27|SortNext|0|32|
28|MemIncr|0|31|
29|SortCallback|3|0|
30|Goto|0|27|
31|Pop|1|0|
32|SortReset|0|0|
33|Halt|0|0|
sqlite> explain SELECT * from Mactor WHERE id = (SELECT MAX(id) FROM
Mactor);
addr|opcode|p1|p2|p3
0|VerifyCookie|0|31|
1|Integer|0|0|
2|OpenRead|1|3|Mactor
3|Last|1|0|
4|Recno|1|0|
5|MemStore|0|1|
6|Goto|0|7|
7|Close|1|0|
8|ColumnName|0|0|id
9|ColumnName|1|0|name
10|ColumnName|2|1|comment
11|ColumnName|3|0|INTEGER
12|ColumnName|4|0|TEXT
13|ColumnName|5|0|TEXT
14|Integer|0|0|
15|OpenRead|0|3|Mactor
16|MemLoad|0|0|
17|MustBeInt|1|23|
18|NotExists|0|23|
19|Recno|0|0|
20|Column|0|1|
21|Column|0|2|
22|Callback|3|0|
23|Close|0|0|
24|Halt|0|0|
sqlite>explain SELECT * FROM Mactor ORDER BY id DESC LIMIT 1;
addr|opcode|p1|p2|p3
0|ColumnName|0|0|id
1|ColumnName|1|0|name
2|ColumnName|2|1|comment
3|Integer|-1|0|
4|MemStore|0|1|
5|ColumnName|3|0|INTEGER
6|ColumnName|4|0|TEXT
7|ColumnName|5|0|TEXT
8|Integer|0|0|
9|OpenRead|0|3|Mactor
10|VerifyCookie|0|31|
11|Rewind|0|20|
12|Recno|0|0|
13|Column|0|1|
14|Column|0|2|
15|SortMakeRec|3|0|
16|Recno|0|0|
17|SortMakeKey|1|0|-
18|SortPut|0|0|
19|Next|0|12|
20|Close|0|0|
21|Sort|0|0|
22|SortNext|0|27|
23|MemIncr|0|26|
24|SortCallback|3|0|
25|Goto|0|22|
26|Pop|1|0|
27|SortReset|0|0|
28|Halt|0|0|
sqlite>
