CODEWITHSUNDEEP
sqliteexcept
nour etienne huens
nour etienne huens

Reputation: 3

Remove all the rows except one with the EXCEPT SQLite command

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.

Upvotes: 0

Views: 223

Answers (2)

forpas
forpas

Reputation: 164089

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
)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

I would use ROW_NUMBER here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY LENGTH(name), name) rn_min,
              ROW_NUMBER() OVER (ORDER BY LENGTH(name) DESC, name) rn_max
    FROM character
)

SELECT name, LENGTH(name) AS LEN
FROM cte
WHERE 1 IN (rn_min, rn_max)
ORDER BY LENGTH(name);

Upvotes: 1

Related Questions