Android SQLite: Select No Duplicates in SQL Query

Question

I have a database full of values and one of the columns in an origin (where the particular item comes from). I am trying to populate a spinner with all the origins listed in the database, but obviously I only want them repeated once. I am trying to use the sql statement:

String sql = "SELECT _id, origin FROM items GROUP BY origins";

But I am also using a custom SimpleCursorAdapter to populate the spinner and because of that I also need to query the _id column. When I add the _id column to the query it produces a query with all the repeated origins because the id makes it to where no row is a duplicate. What do I need to do to pass both columns, but remove the duplicates? Being able to organize them alphabetically would also be great!

Preli · Accepted Answer · 2011-08-07 02:27:00Z

3

You get a view with duplicate origin values, since every origin value can "belong" to several ids.

I don't think that the following is a "clean" solution but it gives you no duplicate values along with the highest id corresponding to each origin value:

Select origin, max(_id) from items group by origin

answered Aug 7, 2011 at 2:27

Preli

3,05111 gold badges39 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ryandlf Over a year ago

This does not work, because in my situation the custom simplecursoradapter needs the entire _id column and it throws an exception.

Preli Over a year ago

I don't know enought about the simplecursoradapter, but probably it is confused because the name differes try instead of "max(_id)" -> "max(_id) as _id".

swopecr · Accepted Answer · 2011-08-07 03:19:15Z

You might also consider normalizing your database. Rather than repeating the origins multiple times in the table, you could create a separate origins reference table. Then, in your original table, you could reference the origin by it's primary key. For example:

origins
***********************
id     name
-----------------------
1      origin_1
2      origin_2
3      origin_3
***********************

detail_data
**************************
id      origin_id     data
1       1             ...
2       1             ...
3       3             ...
4       3             ...
**************************

Then if you only wanted origins for which data existed, you could do something like:

SELECT DISTINCT o.* 
FROM origins AS o 
JOIN detail_data AS dd ON (dd.origin_id = o.id)
ORDER BY o.name ASC;

I generally prefer a normalized database because normalization can offer advantages such as a reduced footprint size, cleaner data, and a more flexible structure. I will admit, however, that normalization seems less beneficial with Android compared to traditional database systems (i.e., desktops, servers). And normalization has disadvantages, too, such as more complex queries (as we can see above). For maximizing flexibility, maintainability and robustness for the long-term, though, normalization may be the better approach.

+1 for suggesting normalization - definitely a good approach to databse design.

Pikaling · Accepted Answer · 2011-08-07 02:31:54Z

1

To add to Preli's answer, if you want them in alphabetical order, just change it to this:

SELECT origin, MAX(_id) FROM items GROUP BY origin ORDER BY origin

edited Aug 7, 2011 at 2:31

answered Aug 7, 2011 at 2:20

Pikaling

8,3123 gold badges29 silver badges44 bronze badges

Collectives™ on Stack Overflow

Android SQLite: Select No Duplicates in SQL Query

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related