DISTINCT problem with PDO and SQLite table

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 :frowning:

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.

Please post a query with DISTINCT that gives you zero as result

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'

echo count($result) . "<hr >";
var_dump($result);

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?

Bumping to give update

The quotes was me typing out a substitue line in a hurry while not thinking (mixing PHP with SQL think)

And by “substitute”, I mean yes, I was being less than exact describing what I’m really up to.

“last_name” isn’t a text string, it’s a group of numbers each with 256 possible values that I need to split apart.

Anyway, I revisited and managed to get what I need by losing DISTINCT and using GROUP BY instead.

SELECT unique_triads FROM (SELECT get_first_three_letters(last_name) AS unique_triads FROM name_table WHERE last_name LIKE ?) GROUP BY unique_triads

where “?” is the PDO bound statement placeholder.

shame on you for misleading obfuscation

2 Likes