Re: [sqlite] Get Max Samples

[Rick Ratchford]

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

[Igor Tandetnik]

Rick Ratchford 
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

Re: [sqlite] Get Max Samples

[Matt Sergeant]

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

[Pavel Ivanov]

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 Ratchford
 wrote:
>
> 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