CODEWITHSUNDEEP
androidsqlitespinner
ryandlf
ryandlf

Reputation: 28555

Android SQLite: Select No Duplicates in SQL Query

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!

Upvotes: 0

Views: 3332

Answers (3)

swopecr
swopecr

Reputation: 91

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.

Upvotes: 2

Pikaling
Pikaling

Reputation: 8312

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

Upvotes: 1

Preli
Preli

Reputation: 3031

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

Upvotes: 3

Related Questions