Retrieving Distinct values from SQLite

Problems with WiFi, SQLite ,Bluetooth, WiMax, Proxies, etc...

Retrieving Distinct values from SQLite

Postby Croccy22 » Tue Aug 11, 2009 11:18 pm

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.
Croccy22
Developer
Developer
 
Posts: 31
Joined: Wed Dec 03, 2008 3:15 pm

Top

Postby Croccy22 » Tue Aug 11, 2009 11:45 pm

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.
Croccy22
Developer
Developer
 
Posts: 31
Joined: Wed Dec 03, 2008 3:15 pm

Postby nmc » Wed Aug 12, 2009 12:15 am

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.
nmc
Senior Developer
Senior Developer
 
Posts: 154
Joined: Thu Nov 27, 2008 8:30 pm
Location: Germany

Postby Croccy22 » Wed Aug 12, 2009 7:17 am

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.
Croccy22
Developer
Developer
 
Posts: 31
Joined: Wed Dec 03, 2008 3:15 pm

Postby nmc » Wed Aug 12, 2009 9:42 am

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 8)
nmc
Senior Developer
Senior Developer
 
Posts: 154
Joined: Thu Nov 27, 2008 8:30 pm
Location: Germany

Postby Croccy22 » Wed Aug 12, 2009 11:40 am

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.
Croccy22
Developer
Developer
 
Posts: 31
Joined: Wed Dec 03, 2008 3:15 pm

Top

Postby nmc » Wed Aug 12, 2009 6:49 pm

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
nmc
Senior Developer
Senior Developer
 
Posts: 154
Joined: Thu Nov 27, 2008 8:30 pm
Location: Germany

Postby Croccy22 » Wed Aug 12, 2009 7:16 pm

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:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. public void onListItemClick(ListView parent, View v, int position, long id) {
  2.  
  3.                 super.onListItemClick(parent, v, position, id);
  4.  
  5.  
  6.  
  7.                 Object o = adapter.getItem(position);
  8.  
  9.             selectedroom = o.toString();
  10.  
  11.            
  12.  
  13.             startActivity(new Intent(this, RoomDisplay.class));        
  14.  
  15. }
Parsed in 0.032 seconds, using GeSHi 1.0.8.4


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.
Croccy22
Developer
Developer
 
Posts: 31
Joined: Wed Dec 03, 2008 3:15 pm

Postby Croccy22 » Wed Aug 12, 2009 7:35 pm

Just sorted it:

Syntax: [ Download ] [ Hide ]
Using java Syntax Highlighting
  1. public void onListItemClick(ListView parent, View v, int position, long id) {
  2.  
  3. super.onListItemClick(parent, v, position, id);
  4.  
  5.  
  6.  
  7. Cursor c = (Cursor) getListView().getItemAtPosition(position);
  8.  
  9. selectedroom = c.getString(1);
  10.  
  11.                            
  12.  
  13. startActivity(new Intent(this, RoomDisplay.class));            
  14.  
  15. }
Parsed in 0.032 seconds, using GeSHi 1.0.8.4


Matt.
Croccy22
Developer
Developer
 
Posts: 31
Joined: Wed Dec 03, 2008 3:15 pm

Top

Return to Networking & Database Problems

Who is online

Users browsing this forum: No registered users and 7 guests