I want to sort data in my Android Sqlite alphabetically and then choose only the first row for each alphabet. e.g. If the result set consists of Abc, Abd, Abe, Bbc, Bbd I want only Abc, Bbc. Is it possible using SQL?
Asked
Active
Viewed 525 times
1 Answers
1
This should work for SQLite.
SELECT min(column_name)
FROM table_name
GROUP BY substr(column_name, 1, 1)
The substring syntax may differ on other systems. It groups your data by the first character, then returns the alphabetical minimum of each in the group.
-- edit (1/2)
If you need the entire row, it's probably best to use the results of the above query to find the entire row. Like so:
SELECT table_name.*
FROM table_name
JOIN
(SELECT min(column_name) AS column_name
FROM table_name
GROUP BY substr(column_name, 1, 1)) sub_query
ON table_name.column_name = sub_query.column_name
GROUP BY column_name
HAVING table_name.ROWID = min(table_name.ROWID)
-- edit (2/2)
Added the last two lines (GROUP BY and HAVING). They are needed to avoid duplicates. If you have duplicate entries for column_name then the one with the lowest ROWID is chosen.
Jozua
- 1,274
- 10
- 18
-
I don't want minimum , I want EXACTLY one row. – everydayapps Mar 25 '14 at 14:47
-
Just try it. If your dataset is Abc; Abd; Abe; Bbc; Bbd; this query will return Abc; Bbc. – Jozua Mar 25 '14 at 14:55
-
How to modify this if I want to sort by NAME , but also want to get another column, say CATEGORY ? – everydayapps Mar 25 '14 at 15:27
-
Thanks. I am yet to try this due to some other priorities. Will let you know asap. – everydayapps Mar 26 '14 at 04:18
-
If there are duplicate 'column_name' values then the query will return both. You want the 'first row' in that case? – Jozua Mar 31 '14 at 14:35
-
Yes. Exactly one row. – everydayapps Mar 31 '14 at 15:30
-
This works fine with MySQL but with SQLite the duplicates are completely removed. – everydayapps Apr 01 '14 at 09:08
-
Not in my tests, give me an example of the data you're having trouble with. – Jozua Apr 01 '14 at 13:50
-
I observe this problem on 2.3.6 but not on 4.4.2. Which version did you test on? – everydayapps Apr 02 '14 at 04:57
-
I tested on OSX running SQLite 3.7.13. Here you can see an overview of SQLite versions on android platforms: http://stackoverflow.com/questions/2421189/version-of-sqlite-used-in-android – Jozua Apr 02 '14 at 09:15
-
Any clues for me to explore further ? Any chance you could test on android emulator/phone having 2.3.6 ? I am at my wit's end :-( – everydayapps Apr 02 '14 at 10:28