Remove all the rows except one with the EXCEPT SQLite command

Question

From a dataset character that has a name column, I want to query the two names in with the shortest and longest names, as well as their respective lengths and when there is more than one smallest or largest name, I choose the one that comes first when ordered alphabetically.

With that query, I get all the shortest and longest names (A)

SELECT 
    name, LENGTH(name) AS LEN 
FROM 
    character 
WHERE 
    length(name) = (SELECT MAX(LENGTH(name)) FROM character) 
    OR length(name) = (SELECT MIN(LENGTH(name)) FROM character) 

With this one, I get all the shortest names except the first alphabetically ordered one (B)

SELECT 
    name, LENGTH(name) AS LEN 
FROM 
    character 
WHERE 
    length(name) = (SELECT MIN(LENGTH(name)) FROM character) 
ORDER BY 
    name DESC
LIMIT 10 OFFSET 2;

When I try to remove B from A

A EXCEPT B

I would expect to keep the first shortest name but It does not appear.

Answer 1

When you set OFFSET 2 in your B query, you don't get:
all the shortest names except the first 1 alphabetically ordered
Instead you get:
all the shortest names except the first 2 alphabetically ordered,
because this is what OFFSET 2 does: it skips the first 2 rows.

Also another problem with your code is the ORDER BY clause in your B query.
If you have this:

SELECT name,LENGTH(name) AS LEN FROM character 
WHERE length(name) = (select max( LENGTH(name)) from character ) 
or length(name) = (select min( LENGTH(name)) from character) 
EXCEPT
SELECT name,LENGTH(name) AS LEN FROM character 
WHERE length(name) = (select min( LENGTH(name)) from character) 
ORDER BY name desc LIMIT 10 OFFSET 2;

you may think that the ORDER BY clause (and LIMIT and OFFSET) is applied only to your B query, but this is not how it is interpreted.
Actually ORDER BY (and LIMIT and OFFSET) is applied to the whole query after the rows are returned.

To get the results that you want by using code similar to yours you must use a subquery to wrap your B query, like this:

SELECT name,LENGTH(name) AS LEN FROM character 
WHERE length(name) = (select max( LENGTH(name)) from character ) 
or length(name) = (select min( LENGTH(name)) from character) 
EXCEPT
SELECT * FROM (
  SELECT name,LENGTH(name) AS LEN FROM character 
  WHERE length(name) = (select min( LENGTH(name)) from character) 
  ORDER BY name desc LIMIT 10 OFFSET 1
)