I could use PHP to get what I want, but I’m hoping there is a way to craft a query that will do it without needing to deal with a massive array.
I have an SQLite table with names, lots of them. What I’m hoping to do is get a unique set of the first three letters in each.
PDO lets me create a function that will get the first three letters, this works.
SELECT unique_triads FROM (SELECT get_first_three_letters('last_name') AS unique_triads FROM 'name_table')
This query returns the same result set as SELECT get_first_three_letters('last_name') FROM 'name_table'
* Note, unique_triads is what I’m hoping for, NOT what I’m getting
However the result set is massive and contains many replicates.
I’ve tried using DISTINCT in various syntax but no matter what I’ve tried, with it in the query it keeps returning Zero.
I’ve also tried GROUP BY in various syntax with the same outcome.
eg. both of these SELECT DISTINCT unique_triads FROM (SELECT get_first_three_letters('last_name') AS unique_triads FROM 'name_table') SELECT DISTINCT get_first_three_letters('last_name') FROM 'name_table'
my experience with SQLite is zilch, but how come you have the column and table names inside single quotes? in other databases, that gives you a string, not a column or table
also, why this weird get_first_three_letters function? doesn’t SQLite have a LEFT function? or even the sql standard SUBSTRING function?
edit: i just googled, yes, SQLite has a builtin SUBSTR function… why don’t you try that, mkay?