How can I select data with no duplicate the "First Letter" value? My table has a column named "title_raw" with data arrange follow "A, B, C, ..." I want my data display something like this
Select (title_raw no duplicate first letter) from SONGS
views:
265answers:
2
+1
A:
select substr(col, 0, 1) as Letter, count(primary_key) as Frequency
from table
group by Letter
Don't know if you can use column alias' in group by with SQLite. This will give you any first letters that are unique. Then use that as a subquery for:
select data
from table
where substr(data, 0, 1) IN (subquery)
That should work
Chris
2009-09-24 14:06:19
What's "subquery" ? I don't understand?
Dennie
2009-09-24 14:15:18
I try your query but I don't know why It just work with number data?
Dennie
2009-09-24 14:26:44
First try running the first query, see if that works. You may need to change group by Letter to be group by substr(col, 0, 1)Once that works replace subquery with the first query. It fetches all the first letters that are unique and then selects the rows starting with those letters.
Chris
2009-09-24 14:55:48