CODEWITHSUNDEEP
androidsqlitedistinct
Sadeq Shajary
Sadeq Shajary

Reputation: 437

how to use distinct in sqlite and cursor without _id essential column?

I have an table that i should use this query for right answer:

select distinct IDPAGE,TIT from contents where NUM=1;

and send this cursor to cursorAdapter. As you know, in this cursor should be an _id column. if not, app not started. but i just want distinct my tables and not _id col. i want a query like:

select rowid _id distinct IDPAGE,TIT from contents where NUM=1;

in sql , i can use DENSE_RANK() but not in sqlite. please help me...

Upvotes: 2

Views: 625

Answers (1)

CL.
CL.

Reputation: 180101

The Android framework requires a unique _id column in some cursors, but this column must be unique only in this particular result set. So if you have a column that is unique in the results you're selecting, you can use it also as the _id column:

SELECT IDPAGE AS _id, IDPAGE, TIT FROM ... WHERE ...;

(The DISTINCT is then not needed because a unique column never has duplicates.)

If you do not have a single unique column, you must also get the rowid (or whatever your INTEGER PRIMARY KEY column is named) from the table. To still get distinct values for the other columns, you have to replace DISTINCT with GROUP BY:

SELECT MIN(rowid) AS _id, IDPAGE, TIT
FROM contents
WHERE NUM = 1
GROUP BY IDPAGE, TIT;

Upvotes: 2

Related Questions