Hi there, I’m trying to figure out a clever way to compare the string values that I got stored in a SQLite table column. The target is to detect how many times a string value recurs, and so to compare the number in order to find the one that recurs the greater number of time.
What makes it complicated is that I do not use a defined list of values, and the comparison should be done - let me say - “in a dynamic way”.
In my following example my target would be to get " Sales Support Agent ".
Something like this, maybe? Note that I’m not a SQL expert:
SELECT Title, COUNT(*) FROM TableName GROUP BY Title;
Executing that should give you a result with each Title and a count value:
______________________________
| Title | Count |
| -----------------------------
| General Manager | 1 |
| Sales Manager | 1 |
| etc... | |
'------------------------------'Thank you Anthony for your contribution, I don’t know if it will solve my case but I can’t believe I googoled for a few hours finding nothing pointing to " GROUP BY ". 
I got this:
SELECT title, COUNT(*) as count FROM employees GROUP BY title ORDER BY count DESC;
I was thinking to use ‘Limit 1’ but you may want to know if you have 2 records or more with the same max number.
| Title | count |
|---|---|
| Sales Support Agent | 3 |
| IT Staff | 2 |
| General Manager | 1 |
| IT Manager | 1 |
| Sales Manager | 1 |
Some information that may help:
https://www.sqlitetutorial.net/tryit/query/sqlite-count-function/#3
Thank you Alberto, I will pick a single result in any case, but I’d prefer the selection to be not Alphabetically Pre-established by default. Anyway this seems to be the most direct solution so far, even if it won’t allow me to manage the output result as you remarked in your comment. Great limitation.
Very very interesting reading. The combination of COUNT + GROP BY should led me directly to the expected goal. I will post my solution ASAP. Thank you Anthony!
Happy to help! Please be sure to mark a solution so that others can easily identify what worked for you.
Frankly the most of my handicap is due to the fact that I never be sure if a general topic like the one you posted here will works for Xojo or not (same image I used for my example in yours). I learned how the concept of Deprecation works and looking for Official Xojo Documentation only is quite limiting in most of cases.
Understandable. Not everything works the way you’d expect, and there can often be caveats. SQL, generally speaking, should just work*.
*I mean, there are bugs from time-to-time, but if you run in to one just post it up and we’ll try to help. Great community we have here.
" Great community we have here. "
Absolutely agree with you.
Ok, I finally reach the goal using the following expression:
Var sql As String = "SELECT Title FROM Table GROUP BY Title ORDER BY COUNT(*) DESC"
I still need to manage the case when two or more GROUPS meet the parameter of SELECION but that’s a start.