views:

45

answers:

1

I'm building a list that is sorted alphabetically. The list should show a divider containing the Letter that the following icons belong to.

I'm using a Cursoradapter that contains the already sorted result from the database. I plan on adding the divider to the list item and set it to visible if the shown letter changes. How do I know that I have a new Letter? Is there a better way then going forward or backward in the cursor and check if the item I show is the end or beginning of a new group?

+1  A: 

You can do that in SQL query that provides data for Cursor.

Suppose we have person(name varchar) table.

Instead of asking just:

SELECT name FROM person ORDER BY name;

one can query:

SELECT p.name, CASE(
    SELECT COUNT(*) 
    FROM person 
    WHERE name<p.name and SUBSTR(name, 1, 1)=SUBSTR(p.name,1 , 1)
    ) WHEN 0 THEN 1 ELSE 0 END isFirst 
FROM person p 
ORDER BY name;

that gives (name, isFirst) resultset - each item starting with "new" letter will have isFirst set to 1 indicating start of new group.

edit: Example:

CREATE TABLE person(name varchar);
INSERT INTO person SELECT 'Jim' UNION SELECT 'Tom' UNION SELECT 'Ben' UNION SELECT 'John' UNION SELECT 'Alice' UNION SELECT 'Chris' UNION SELECT 'Anna' UNION SELECT 'Jerry';
SELECT p.name, CASE(
    SELECT COUNT(*) 
    FROM person 
    WHERE name<p.name and SUBSTR(name, 1, 1)=SUBSTR(p.name,1 , 1)
    ) WHEN 0 THEN 1 ELSE 0 END isFirst 
FROM person p 
ORDER BY name;

gives:

Alice|1
Anna|0
Ben|1
Chris|1
Jerry|1
Jim|0
John|0
Tom|1
Gawcio
Great idea but the sample is not working. substring(name) and substring(p.name) are always the same
Janusz
Are you sure it's not working? Look at the example and the result...
Gawcio
Thanks for the example it is working now i overlooked the person p thing at the end.
Janusz
By the way you can Re-tag question adding 'sql' and 'sqlite' tags.
Gawcio