Hi,

I have a SQLite database that has a Table in it which has a column namd 'Room'. There are many rows in the table each of which has a 'room'. Some of the rooms are different like Bedroom, Lounge etc but there can be multiple occurances of a room such as Lounge.

I now have a ListActivity which is bound to the daabse and displays all of the Rooms. However I only want it to diplay each type of room once. So even if there are 30 occurances of 'Lounge' I only want it displayed once in the listview.

Any ideas on an easy way of doing this? I know in SQL you can do a Select Distinct but i'm not sure if here is an equivilent in SQLite. Either that or I guess it will be a case of parsing the results and removing duplicates somehow.

Thanks,

Matt.

OK, it looks like SQLite does support the DISTINCT query so I just need to do a raw SELECT statement, however I seem to have another issue where If I try to only select the room I get an exception error, but if I select the _ID it works fine?

If I do a simple select * and then display just the room it works fine but if I do a select ROOM then I get an exception?

It's late and i'm prob doing something stupid but if anyone has any ideas it would be a great help. Going to go to bed now and take another look tomozzo!

cheers, Matt.

OK, it looks like SQLite does support the DISTINCT query

Thats right. See www.sqlite.org for a good documentation.

If I do a simple select * and then display just the room it works fine but if I do a select ROOM then I get an exception?

"select *" includes the column named "_id" while "select room" does not.

A CursorAdapter needs a column with this name, while directly fetching the rows and inserting the data in an array or a list works just fine with (or without, for that matter) any special column sets.

If you have an id column with a different name, you can easily use "select my_entry_id as _id from my_table" to make a CursorAdapter work.

Hi,

Thanks for the help so far. I thought it must be something to do with that _ID field.

So my next question is if each row has _ID, Device_Location, Device_Type. How can I do a "Select Distinct Device_Location from Device_Table" and then put the result into a listactivity without it crashing?

Thanks, Matt.

How can I do a "Select Distinct Device_Location from Device_Table" and then put the result into a listactivity without it crashing?

1. Use a BaseAdapter with a list filled from direct fetching for your listview

2. I have not tried the following in this special context, but you could try using
select distinct 1 as _id, Device_Location from Device_Table
with your CursorAdapter

Solution 2 is to be classified as a hack, so i would recomment using solution 1

Hi,

Thanks again. Have you got any snippets of code to demonstrate the first method. I'm still new to rpogramming in android so trying to get my head around everything.

Say we have a database as follows:

_ID NAME
1 Matt
2 Bob
3 John
4 Matt

What would I need to fill a ListActivity with the database contents but only the Unique Names, So the listactivity would end up being filled with Matt, Bob, John.

I'll go and do some reading up on BaseAdapters as i've not come scross those yet

Thanks, Matt.

Have you got any snippets of code to demonstrate the first method.

String sql = "select distinct room from my_table order by room";
Cursor c = TheDB.rawQuery(sql, null);
...and then use the Cursor methods to fetch the rows, put them into a string-array;
finally, use a StringAdapter for your listview

Thanks alot.

The first hack method works fine but I'll look at the second method as it may also solve my next related issue.

I have now filled the ListView out with my unique contents from mY Database.

When I click one of the items I get it's ListView ID 0-5 for example. I now wan to retrieve the Test form the Item that is selected.

I found some code which is this:



but when I use that, instead of returning the text I would expec it to I get android.databse.sqlite.SQLiteCursor@43746ce0. What on Earth is that?? I've had a look around and can't find any eaxmples of how to retrieve the string value of the selected item when using a cursor adapter?

Matt.

Just sorted it:



Matt.