How to query the most duplicate data column in SQLite Android-CodePudding

I'm having problem querying SQLite database in android. The way I do it now is only ORDER BY but it doesn't work, only the sorting function.

Cursor cursor = DB.rawQuery("SELECT * FROM Userdetails ORDER BY ID DESC", null);

enter image description here

My question is how to query all the most similar values in the ID column, in this case 1ABC occurs at most so it should appear twenty-two times.

CodePudding user response：

Please be clearer with your question and what you wanna do. Also, this isn't really a java related question. However I rewrote a solution I found online, hope this helps

SELECT
    * 
FROM 
    yourDatabase
ORDER BY 
    COUNT(*) OVER (PARTITION BY ID) DESC;

CodePudding user response：

1- You should group by ID and count each items:

SELECT *, COUNT(*) AS count FROM Userdetails GROUP BY ID

2- Then give it a name and use its result for sorting:

SELECT * FROM ( SELECT *, COUNT(*) AS count FROM Userdetails GROUP BY ID ) innerQuery 
ORDER BY innerQuery.count DESC

and in your code sample:

Cursor cursor = DB.rawQuery("SELECT * FROM ( SELECT *, COUNT(*) AS count FROM Userdetails GROUP BY ID ) innerQuery ORDER BY innerQuery.count DESC", null);

CodePudding user response：

I'm guessing you really don't need all columns for this, so don't use "*". Also, that would likely disrupt the COUNT and DISTINCT for your needs.

So instead of ORDER BY, use GROUP BY to get the info you need. You still need ORDER BY to get the list in order and add in LIMIT to get the largest amount of duplicates.

SELECT DISTINCT ID, COUNT(PROFILE) AS COUNT FROM USERDETAILS
GROUP BY ID  
ORDER BY COUNT desc  
LIMIT 1;

CodePudding user response：

If you don't care about ties in the number of duplicates then use a subquery in the WHERE clause:

SELECT * 
FROM Userdetails 
WHERE ID = (SELECT ID FROM Userdetails GROUP BY ID ORDER BY COUNT(*) DESC LIMIT 1);