Top X Values Per Quarter

[Guest]

I know there are several questions on this subject, but I can't seem to grasp
the concept here.

I'm trying to design a query that shows the Top X Revenue dollars Per
Quarter, Per Region (East and West). So if I wanted the top 10, the query
would show 40 records for each Region (10 for each quarter).

Here's how I have my SQL set up now:

SELECT TOP 10 [Area], [Account], [ECG Revenue], [AGS Revenue], [Signed
Date], [Rev Rec Date], [Rev Rec Quarter], [Product], [OTN]
FROM [NAR All]
WHERE [Rev Rec Quarter]="2nd Quarter"
ORDER BY [ECG Revenue] DESC , [OTN];

I try to mess with the WHERE clause by putting an "AND", or an "OR", but
that does not work.

Please help.
Frank

 

[Brian Camire]

You might try a query whose SQL looks something like this:

SELECT
[Area],
[Account],
[ECG Revenue],
[AGS Revenue],
[Signed Date],
[Rev Rec Date],
[Rev Rec Quarter],
[Product],
[OTN]
FROM
[NAR All]
WHERE
[ECG Revenue]
IN
(SELECT TOP 10
[Self].[ECG Revenue]
FROM
[NAR All] AS [Self]
WHERE
[Self].[Area] = [NAR All].[Area]
AND
[Self].[Rev Rec Quarter] = [NAR All].[Rev Rec Quarter]
ORDER BY
[Self].[ECG Revenue] DESC);

There's no "Region" field so I assume you by "Region" you mean "Area".

The query above may return more than 10 records for each Region and Quarter
if there are "ties".

I noticed your query also orders by the "OTN" field. Was this meant as a
"tie-breaker"? If it's unique, at least within each Region and Quarter,
then, to break the ties, you might try something like this instead:

SELECT
[Area],
[Account],
[ECG Revenue],
[AGS Revenue],
[Signed Date],
[Rev Rec Date],
[Rev Rec Quarter],
[Product],
[OTN]
FROM
[NAR All]
WHERE
[OTN]
IN
(SELECT TOP 10
[Self].[OTN]
FROM
[NAR All] AS [Self]
WHERE
[Self].[Area] = [NAR All].[Area]
AND
[Self].[Rev Rec Quarter] = [NAR All].[Rev Rec Quarter]
ORDER BY
[Self].[ECG Revenue] DESC,
[Self].[OTN]);

 

[Guest]

Thank you! Yes, Area = Region and the OTN was the tie-breaker.

It works great, but not sure what [Self] means.

Thanks again.
Frank

You might try a query whose SQL looks something like this:

SELECT
[Area],
[Account],
[ECG Revenue],
[AGS Revenue],
[Signed Date],
[Rev Rec Date],
[Rev Rec Quarter],
[Product],
[OTN]
FROM
[NAR All]
WHERE
[ECG Revenue]
IN
(SELECT TOP 10
[Self].[ECG Revenue]
FROM
[NAR All] AS [Self]
WHERE
[Self].[Area] = [NAR All].[Area]
AND
[Self].[Rev Rec Quarter] = [NAR All].[Rev Rec Quarter]
ORDER BY
[Self].[ECG Revenue] DESC);

There's no "Region" field so I assume you by "Region" you mean "Area".

The query above may return more than 10 records for each Region and Quarter
if there are "ties".

I noticed your query also orders by the "OTN" field. Was this meant as a
"tie-breaker"? If it's unique, at least within each Region and Quarter,
then, to break the ties, you might try something like this instead:

SELECT
[Area],
[Account],
[ECG Revenue],
[AGS Revenue],
[Signed Date],
[Rev Rec Date],
[Rev Rec Quarter],
[Product],
[OTN]
FROM
[NAR All]
WHERE
[OTN]
IN
(SELECT TOP 10
[Self].[OTN]
FROM
[NAR All] AS [Self]
WHERE
[Self].[Area] = [NAR All].[Area]
AND
[Self].[Rev Rec Quarter] = [NAR All].[Rev Rec Quarter]
ORDER BY
[Self].[ECG Revenue] DESC,
[Self].[OTN]);

I know there are several questions on this subject, but I can't seem to grasp
the concept here.

I'm trying to design a query that shows the Top X Revenue dollars Per
Quarter, Per Region (East and West). So if I wanted the top 10, the query
would show 40 records for each Region (10 for each quarter).

Here's how I have my SQL set up now:

SELECT TOP 10 [Area], [Account], [ECG Revenue], [AGS Revenue], [Signed
Date], [Rev Rec Date], [Rev Rec Quarter], [Product], [OTN]
FROM [NAR All]
WHERE [Rev Rec Quarter]="2nd Quarter"
ORDER BY [ECG Revenue] DESC , [OTN];

I try to mess with the WHERE clause by putting an "AND", or an "OR", but
that does not work.

Please help.
Frank

 

[Brian Camire]

The query includes two "copies" of "NAR All" -- one in the FROM clause of
the outermost query, and another in the FROM clause of the innermost query
(in the IN). "Self" is just an "alias" (that I happend to choose) for the
second instance of "NAR All" so that you (and Access/Jet) can tell them
apart.

Thank you! Yes, Area = Region and the OTN was the tie-breaker.

It works great, but not sure what [Self] means.

Thanks again.
Frank

You might try a query whose SQL looks something like this:

SELECT
[Area],
[Account],
[ECG Revenue],
[AGS Revenue],
[Signed Date],
[Rev Rec Date],
[Rev Rec Quarter],
[Product],
[OTN]
FROM
[NAR All]
WHERE
[ECG Revenue]
IN
(SELECT TOP 10
[Self].[ECG Revenue]
FROM
[NAR All] AS [Self]
WHERE
[Self].[Area] = [NAR All].[Area]
AND
[Self].[Rev Rec Quarter] = [NAR All].[Rev Rec Quarter]
ORDER BY
[Self].[ECG Revenue] DESC);

There's no "Region" field so I assume you by "Region" you mean "Area".

The query above may return more than 10 records for each Region and Quarter
if there are "ties".

I noticed your query also orders by the "OTN" field. Was this meant as a
"tie-breaker"? If it's unique, at least within each Region and Quarter,
then, to break the ties, you might try something like this instead:

SELECT
[Area],
[Account],
[ECG Revenue],
[AGS Revenue],
[Signed Date],
[Rev Rec Date],
[Rev Rec Quarter],
[Product],
[OTN]
FROM
[NAR All]
WHERE
[OTN]
IN
(SELECT TOP 10
[Self].[OTN]
FROM
[NAR All] AS [Self]
WHERE
[Self].[Area] = [NAR All].[Area]
AND
[Self].[Rev Rec Quarter] = [NAR All].[Rev Rec Quarter]
ORDER BY
[Self].[ECG Revenue] DESC,
[Self].[OTN]);

I know there are several questions on this subject, but I can't seem

to
grasp

the concept here.

I'm trying to design a query that shows the Top X Revenue dollars Per
Quarter, Per Region (East and West). So if I wanted the top 10, the query
would show 40 records for each Region (10 for each quarter).

Here's how I have my SQL set up now:

SELECT TOP 10 [Area], [Account], [ECG Revenue], [AGS Revenue], [Signed
Date], [Rev Rec Date], [Rev Rec Quarter], [Product], [OTN]
FROM [NAR All]
WHERE [Rev Rec Quarter]="2nd Quarter"
ORDER BY [ECG Revenue] DESC , [OTN];

I try to mess with the WHERE clause by putting an "AND", or an "OR", but
that does not work.

Please help.
Frank