Max function

[Sylvain]

hello! I am not familiar with SQL complicated queries, but I achieved to retrieve
the number of lines containing the same ID with :


SELECT
COUNT(*) as C1
FROM OMC_P_NBSC_RX_QUAL
GROUP BY BTS_INT_ID, PERIOD_START_TIME, PERIOD_STOP_TIME

HAVING (
PERIOD_START_TIME>=#7/22/2002# AND
PERIOD_STOP_TIME<#7/23/2002#
);


But I just need the max of C1

But the MAX(COUNT(*)) doesn't work.. How can I do then?

THX !!

[Colin McGuigan]

"Sylvain" <sylvain.nasse@ben.nl> wrote in message
news:3d3fafd1$1@10.1.10.29...
>
> hello! I am not familiar with SQL complicated queries, but I achieved to
retrieve
> the number of lines containing the same ID with :
>
>
> SELECT
> COUNT(*) as C1
> FROM OMC_P_NBSC_RX_QUAL
> GROUP BY BTS_INT_ID, PERIOD_START_TIME, PERIOD_STOP_TIME
>
> HAVING (
> PERIOD_START_TIME>=#7/22/2002# AND
> PERIOD_STOP_TIME<#7/23/2002#
> );
>
>
> But I just need the max of C1
>
> But the MAX(COUNT(*)) doesn't work.. How can I do then?

Using Oracle? Not sure if it'd work, but you could try...

SELECT MAX(GroupQuery.C1) FROM (
SELECT COUNT(*) as C1...
...
) GroupQuery

--
Colin McGuigan

[MarkN]

And if that doesn't work then try ordering descending by C1 and get the first
occurance (different for each db).


"Colin McGuigan" <cmcguigan@imany.com> wrote:
>
>"Sylvain" <sylvain.nasse@ben.nl> wrote in message
>news:3d3fafd1$1@10.1.10.29...
>>
>> hello! I am not familiar with SQL complicated queries, but I achieved
to
>retrieve
>> the number of lines containing the same ID with :
>>
>>
>> SELECT
>> COUNT(*) as C1
>> FROM OMC_P_NBSC_RX_QUAL
>> GROUP BY BTS_INT_ID, PERIOD_START_TIME, PERIOD_STOP_TIME
>>
>> HAVING (
>> PERIOD_START_TIME>=#7/22/2002# AND
>> PERIOD_STOP_TIME<#7/23/2002#
>> );
>>
>>
>> But I just need the max of C1
>>
>> But the MAX(COUNT(*)) doesn't work.. How can I do then?
>
>Using Oracle? Not sure if it'd work, but you could try...
>
>SELECT MAX(GroupQuery.C1) FROM (
> SELECT COUNT(*) as C1...
> ...
>) GroupQuery
>
>--
>Colin McGuigan
>
>