Combining DISTINCT and group_concat() with custom delimiters in SQLite3
In this blog post, I'll introduce two useful tools in SQLite3:
I show how they can be used individually, and that you need to watch out when combining them with custom delimiters.
I assume you have a basic understanding of SQL, but mostly you will need to know about the concepts of a table with data and simple
But first, we need a table to work with. For that, we'll create a table of hobbit names, and fill it with a few hobbits from Tolkien's fictional universe:
CREATE TABLE hobbits ( first_name VARCHAR(64), last_name VARCHAR(64) ); INSERT INTO hobbits VALUES ("Frodo", "Baggins"), ("Bilbo", "Baggins"), ("Sam", "Gamgee"), ("Pippin", "Took"), ("Merry", "Brandybuck") ;
To simply select the first and last names of each of the hobbits, the following query can be used:
SELECT first_name, last_name FROM hobbits;
Great! But we can do more interesting things, as demonstrated next.
One of the tools in SQL is the
group_concat() function, which returns a string that is the concatenation of all non-null values of the given result set.
For example, this query concatenates all of the hobbits' first names:
SELECT group_concat(first_name) FROM hobbits;
You can see that, by default, the values are delimited by commas (
To customise this, it is possible to provide an additional argument that sets the delimiter:
SELECT group_concat(first_name, " and ") FROM hobbits ;
Frodo and Bilbo and Sam and Pippin and Merry
That's a little more readable.
Another tool in SQL is the
DISTINCT keyword, which can remove duplicate entries from result sets.
The keen-eyed (or well-read) among you may have observed (or remembered) that Frodo and Bilbo share their last names! This means that when selecting the last names, we would see a duplicate entry. And indeed:
SELECT last_name FROM hobbits;
To remove the duplicate Bagginses from the result set, you can specify that the result set should be made distinct with the
SELECT DISTINCT last_name FROM hobbits;
As expected, only a single Baggins remains in the result.
Concatenating distinct values
So, what if we want to combine
This will concatenate all the distinct last names:
SELECT group_concat(DISTINCT last_name) FROM hobbits ;
Fabulous! We're wielding these tools like a pro! Feeling overly confident, we may even try to customise the delimiter for extra points:
SELECT group_concat(DISTINCT last_name, " and ") FROM hobbits ;
Parse error near line 2: DISTINCT aggregates must have exactly one argument
It looks like the way we use the
DISTINCT keyword confuses the query parser.
It seems like the
last_name, " and " part is interpreted as two arguments for the
Let's try to fix that with parentheses so that the
DISTINCT keyword only applies to
SELECT group_concat((DISTINCT last_name), " and ") FROM hobbits ;
Parse error near line 39: near "DISTINCT": syntax error SELECT group_concat((DISTINCT last_name), " and ") FROM hobbits ; error here ---^
Nope! It seems like this is not allowed.
SELECT grammar, it is indeed the case that it is required for the
DISTINCT keyword to directly follow the opening parenthesis of the
group_concat() (or other) function.
It looks like the
DISTINCT keyword is only allowed in certain contexts, and a special case has been made for adding it in function invocations.
This special case prohibits extra arguments in the function invocation.
So, how do we solve this? Well, an answer on the SQLite Forum provided a solution! It uses a subquery as a workaround:
SELECT group_concat(distinct_last_name, " and ") FROM ( SELECT DISTINCT last_name AS distinct_last_name FROM hobbits );
Baggins and Gamgee and Took and Brandybuck
The inner query creates an intermediate result set that we can use in the outer query. This workaround definitely feels like... well, a workaround. If you find a more elegant solution to this, please let me know!
DISTINCT tools do not work together as I expected when using a custom delimiter.
As Bilbo Baggins would put it: I don't know half of SQL as well as I should like; and I like less than half of it half as well as it deserves.