Thanks Pavel, Matt and Igor. :-) What more could I have provided? It's just a column and I have no idea how group_concat works anyways. That's why I threw that in.
Yes, the largest SET is what I need. Didn't know to call it a SET until after reading your comments. Now to make sure I understand this lesson. To GROUP BY, we're instructing the database to act on the data in these SETS, right? So each SET is then counted by "count(*)" and stored into 'cnt'. Then max(cnt) will return the highest value, which in this case would be the highest 'cnt' or largest size SET in other words. I think I understand. As for my code... sSQL = "SELECT max(cnt) FROM (SELECT count(*) as cnt FROM [" & gsTableName & "] GROUP BY Year)" The above is the ACTUAL code I'm using. My SQL strings need to be assigned to a string variable, such as sSQL shown above. Then I can pass this SQL string variable my VB function that opens a recordset using that SQL statement. Set RsTemp = Cnn.OpenRecordset(sSQL) The "Year" is the actual column that is in place of what I referred to earlier as the SampleNumber. Samples are grouped by YEAR, such as... 1988 1988 1988 1988 1989 1989 1989 1989 1989 ... Thought it easier to ask the question based on SampleNumber rather than Year to remove confusion. Also, the TableName must be inserted into the SQL string via a string variable called gsTableName. That is why you see FROM [" & gsTableName & "] GROUP BY Year in the statement. This seems to do the trick. Thanks ya'll. :-) Rick #>-----Original Message----- #>From: sqlite-users-boun...@sqlite.org #>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Igor Tandetnik #>Sent: Tuesday, November 10, 2009 2:33 PM #>To: sqlite-users@sqlite.org #>Subject: Re: [sqlite] Get Max Samples #> #>Rick Ratchford <r...@amazingaccuracy.com> #>wrote: #>> How would you write the SQL statement that would return the maximum #>> number of a sample? #>> #>> For example, if within the SampleNumber column, the SampleNumber 17 #>> had more records (say there are 23 SampleNumber = 17 in the table, #>> more than any other), you wanted to return the value 23? #> #>select max(c) from ( #> select count(*) c from mytable group by SampleNumber ); #> #>-- or #> #>select count(*) c from mytable #>group by SampleNumber #>order by c desc limit 1; #> #>> Is group_concat used here? #> #>You haven't shown any code. Had you done so, I could have #>easily told you whether or not you used group_concat in it. #> #>Igor Tandetnik #> #>_______________________________________________ #>sqlite-users mailing list #>sqlite-users@sqlite.org #>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users #> #> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
