Check for a series?

How would I craft a query to check whether the resultset was a series?
The basic query is this:
SELECT StepNumber FROM Steps WHERE Range='Midset'

stepnumber is an integer field.
The stepnumbers in the range must form a series starting at 1, i.e. no gaps.
1 2 3 4 5 6 7 8 9

Can I do this in one query?

Thanks...

Re: Check for a series?

ben h wrote:
> How would I craft a query to check whether the resultset was a series?
> The basic query is this:
> SELECT StepNumber FROM Steps WHERE Range='Midset'
>
> stepnumber is an integer field.
> The stepnumbers in the range must form a series starting at 1, i.e.
> no gaps. 1 2 3 4 5 6 7 8 9
>
> Can I do this in one query?
>
What database???
Type and version please, this information is almost always relevant.

See if this helps:
http://www.aspfaq.com/show.asp?id=2516

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Check for a series?

"ben h" wrote in message
news:ODHIfjYoGHA.524@TK2MSFTNGP05.phx.gbl...
> How would I craft a query to check whether the resultset was a series?
> The basic query is this:
> SELECT StepNumber FROM Steps WHERE Range='Midset'
>
> stepnumber is an integer field.
> The stepnumbers in the range must form a series starting at 1, i.e. no
gaps.
> 1 2 3 4 5 6 7 8 9
>
> Can I do this in one query?
>
> Thanks...

Select Max(StepNumber) MaxNum , Min(StepNumber) MinNum, Count(1) Members
From Steps Where Range = 'Midset'

Now MaxNum - MinNum + 1 = Members when StepNumbers are a contigous sequence
of integers. It does assume however that duplicates are not present.

Re: Check for a series?

Anthony Jones wrote on 09 jul 2006 in
microsoft.public.inetserver.asp.db:

>
> "ben h" wrote in message
> news:ODHIfjYoGHA.524@TK2MSFTNGP05.phx.gbl...
>> How would I craft a query to check whether the resultset was a
>> series? The basic query is this:
>> SELECT StepNumber FROM Steps WHERE Range='Midset'
>>
>> stepnumber is an integer field.
>> The stepnumbers in the range must form a series starting at 1, i.e.
>> no
> gaps.
>> 1 2 3 4 5 6 7 8 9
>>
>> Can I do this in one query?
>>
>> Thanks...
>
> Select Max(StepNumber) MaxNum , Min(StepNumber) MinNum, Count(1)
> Members From Steps Where Range = 'Midset'
>
> Now MaxNum - MinNum + 1 = Members when StepNumbers are a contigous
> sequence of integers. It does assume however that duplicates are not
> present.

What does Count(1) do?


--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

Re: Check for a series?

Bob Barrows [MVP] wrote:
> What database???
> Type and version please, this information is almost always relevant.

Access 2000

> See if this helps:
> http://www.aspfaq.com/show.asp?id=2516

That does help!

Cheers,
ben

Re: Check for a series?

"Evertjan." wrote in message
news:Xns97FBBF391E5B5eejj99@194.109.133.242...
> Anthony Jones wrote on 09 jul 2006 in
> microsoft.public.inetserver.asp.db:
>
> >
> > "ben h" wrote in message
> > news:ODHIfjYoGHA.524@TK2MSFTNGP05.phx.gbl...
> >> How would I craft a query to check whether the resultset was a
> >> series? The basic query is this:
> >> SELECT StepNumber FROM Steps WHERE Range='Midset'
> >>
> >> stepnumber is an integer field.
> >> The stepnumbers in the range must form a series starting at 1, i.e.
> >> no
> > gaps.
> >> 1 2 3 4 5 6 7 8 9
> >>
> >> Can I do this in one query?
> >>
> >> Thanks...
> >
> > Select Max(StepNumber) MaxNum , Min(StepNumber) MinNum, Count(1)
> > Members From Steps Where Range = 'Midset'
> >
> > Now MaxNum - MinNum + 1 = Members when StepNumbers are a contigous
> > sequence of integers. It does assume however that duplicates are not
> > present.
>
> What does Count(1) do?
>

Counts how many records in the Steps where Range = 'Midset'

It could be Count(*), Count(StepNumber), Count('PinkElephants'), etc.

>
> --
> Evertjan.
> The Netherlands.
> (Please change the x'es to dots in my emailaddress)