Re: [sqlite] Get Max Samples
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
Re: [sqlite] Get Max Samples
Rick Ratchfordwrote: > 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
Re: [sqlite] Get Max Samples
On Tue, 10 Nov 2009 15:28:30 -0500, Pavel Ivanov wrote: > You're right about max() and group_concat() will not help you either. > You need something like this: > > select max(cnt) > from (select count(*) as cnt from table_name group by SampleNum) That'll give you the count of the largest set. But not the actual value. For that you need to combine it with a HAVING clause. But I'll leave that as an exercise :-) Matt. __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Get Max Samples
You're right about max() and group_concat() will not help you either. You need something like this: select max(cnt) from (select count(*) as cnt from table_name group by SampleNum) Pavel On Tue, Nov 10, 2009 at 3:24 PM, Rick Ratchfordwrote: > > Suppose you had a column called SampleNumber. > > And in this column, you might have... > > 1 > 1 > 1 > 2 > 2 > 2 > 2 > 3 > 3 > 4 > 4 > 4 > 5 > 5 > 5 > 5 > > etc. > > 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? > > If I use max(SampleNum), that will return only the highest value in the > column rather than what I want, right? > > Is group_concat used here? > > > Thank you. > > Rick > > ___ > 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
[sqlite] Get Max Samples
Suppose you had a column called SampleNumber. And in this column, you might have... 1 1 1 2 2 2 2 3 3 4 4 4 5 5 5 5 etc. 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? If I use max(SampleNum), that will return only the highest value in the column rather than what I want, right? Is group_concat used here? Thank you. Rick ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users