0

I'm having a problem querying a 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

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
SaHiRo
  • 17
  • 3
  • Please add your data to the question, so it's easier for us to test against. You might want to check out the [Tour](https://stackoverflow.com/tour) and [How To Ask](https://stackoverflow.com/help/how-to-ask) pages. – computercarguy Dec 17 '21 at 20:10
  • Edit your question and explain better what you want with sample data and expected results as tect and not images. – forpas Dec 17 '21 at 20:21

4 Answers4

0

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);
Mehdi Rahimi
  • 1,453
  • 5
  • 20
  • 31
  • I do it your way but the result i get `ID` column returns two results `1ABC` `2ABC`. My question is how to query all the most similar values ​​in the `ID` column, in which case `1ABC` occurs the most should be queried. – SaHiRo Dec 17 '21 at 20:44
0

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;
Brentspine
  • 274
  • 1
  • 15
0

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;
computercarguy
  • 2,173
  • 1
  • 13
  • 27
  • sorry, when I use your query, Android Studio gives an error. – SaHiRo Dec 17 '21 at 20:58
  • @SaHiRo, what error does it give? – computercarguy Dec 17 '21 at 21:04
  • Oh sorry, just mistyped my column name. But I ran it again and your files didn't return any data. I think `ORDER BY` is already filtered. – SaHiRo Dec 17 '21 at 21:12
  • @SaHiRo, even if it was already filtered, it should still give results. I accidentally had the wrong table name in the query, so I've updated that, if that was the cause of the first error. – computercarguy Dec 17 '21 at 21:17
  • Logcat in Android Studio does not log any results. `ArrayList listProfile = new ArrayList(); ArrayList listID = new ArrayList(); if(cursor.moveToFirst()){ do{ listProfile.add(cursor.getString(0)); listID.add(cursor.getString(1)); }while(cursor.moveToNext()); } Log.i(TAG, "KQ ------ Profile:" + listProfile); Log.i(TAG, "KQ ------ ID:" + listID);` – SaHiRo Dec 17 '21 at 21:22
  • @SaHiRo, if you try it without the `LIMIT 1`, does it still not return any results? – computercarguy Dec 17 '21 at 21:39
  • @SaHiRo, you need to convert your array of strings to a single string before pushing it to a log, or you won't actually see what's in the array. https://stackoverflow.com/questions/5283444/convert-array-of-strings-into-a-string-in-java/5283753 – computercarguy Dec 17 '21 at 21:50
0

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);
forpas
  • 160,666
  • 10
  • 38
  • 76