Nested/Subquery assistance

[Kenny G]

Below is a query that I currently have. I need to produce a subquery
so that the top five of the CodeCount is returned. I appreciate your
help.

SELECT [Procedure (Px)].PX_SURGEON, [Procedure (Px)].PX_CODE,
Count([Procedure (Px)].PX_CODE) AS CodeCount, [Procedure
Reference].PX_TITLE
FROM [Procedure Reference] INNER JOIN [Procedure (Px)] ON [Procedure
Reference].PX_CODE = [Procedure (Px)].PX_CODE
GROUP BY [Procedure (Px)].PX_SURGEON, [Procedure (Px)].PX_CODE,
[Procedure Reference].PX_TITLE
ORDER BY [Procedure (Px)].PX_SURGEON, Count([Procedure (Px)].PX_CODE)
DESC;

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not clear what you want. Do you want the top 5 rows of your query or
a result set with only the top 5 Px_Codes (how would you define the
top 5 PX_Codes - highest count)? Or, do you want the top 5 PX_Codes
for each surgeon? Might be a good idea if you gave an example of what
you expect the result set would look like.

If top 5 of your query just change the SELECT clause like this:

SELECT Top 5 [Procedure (Px)].PX_SURGEON, [Procedure (Px)].PX_CODE,
Count([Procedure (Px)].PX_CODE) AS CodeCount, [Procedure
Reference].PX_TITLE
.... etc. ...

and change the ORDER BY clause like this:

ORDER BY 3 DESC

I prefer to use the column ordinal value in the ORDER BY clause rather
than "Count([Procedure (Px)].PX_CODE)," 'cuz it avoids confusion -
we're sorting by the values in column 3 rather than the results of the
function Count() - which really is the value of column 3. It's just
that I don't have to mentally parse the expression - all I have to do
is see that it will be sorted by the 3rd column.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5cK24echKqOuFEgEQL21gCePc/jf9pQfLQZnR49v7aL4METxk4AoJEc
UwOvWmPmhKOKJpJBUCP2+gKD
=/kro
-----END PGP SIGNATURE-----
Kenny G wrote:

Below is a query that I currently have. I need to produce a subquery
so that the top five of the CodeCount is returned. I appreciate your
help.

SELECT [Procedure (Px)].PX_SURGEON, [Procedure (Px)].PX_CODE,
Count([Procedure (Px)].PX_CODE) AS CodeCount, [Procedure
Reference].PX_TITLE
FROM [Procedure Reference] INNER JOIN [Procedure (Px)] ON [Procedure
Reference].PX_CODE = [Procedure (Px)].PX_CODE
GROUP BY [Procedure (Px)].PX_SURGEON, [Procedure (Px)].PX_CODE,
[Procedure Reference].PX_TITLE
ORDER BY [Procedure (Px)].PX_SURGEON, Count([Procedure (Px)].PX_CODE)
DESC;

[Kenny G]

The current query gives me the total code count in desc order. I need
the top five codes for each surgeon with surgeon number in desc order.
It should look similar to this:

Surgeon PXCode CodeCount PXTitle
100 4013 3217 XXXXXXXX
100 3745 2866 XXXXXXXX
100 913 2313 XXXXXXX
100 1212 1819 XXXXXXX
100 7887 614 XXXXXXX

86 4550 5788 XXXXXX
86 377 3712 XXXXX
86 622 1712 XXXXXXXXX
86 1976 677 XXXXXXX
86 6650 76 XXXXX

I appreciate your help.

Kenny G

MGFoster <[e-mail]> wrote in message news:<nV****************@newsread3.news.pas.earthl ink.net>...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not clear what you want. Do you want the top 5 rows of your query or
a result set with only the top 5 Px_Codes (how would you define the
top 5 PX_Codes - highest count)? Or, do you want the top 5 PX_Codes
for each surgeon? Might be a good idea if you gave an example of what
you expect the result set would look like.

If top 5 of your query just change the SELECT clause like this:

SELECT Top 5 [Procedure (Px)].PX_SURGEON, [Procedure (Px)].PX_CODE,
Count([Procedure (Px)].PX_CODE) AS CodeCount, [Procedure
Reference].PX_TITLE
... etc. ...

and change the ORDER BY clause like this:

ORDER BY 3 DESC

I prefer to use the column ordinal value in the ORDER BY clause rather
than "Count([Procedure (Px)].PX_CODE)," 'cuz it avoids confusion -
we're sorting by the values in column 3 rather than the results of the
function Count() - which really is the value of column 3. It's just
that I don't have to mentally parse the expression - all I have to do
is see that it will be sorted by the 3rd column.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5cK24echKqOuFEgEQL21gCePc/jf9pQfLQZnR49v7aL4METxk4AoJEc
UwOvWmPmhKOKJpJBUCP2+gKD
=/kro
-----END PGP SIGNATURE-----
Kenny G wrote:

Below is a query that I currently have. I need to produce a subquery
so that the top five of the CodeCount is returned. I appreciate your
help.

SELECT [Procedure (Px)].PX_SURGEON, [Procedure (Px)].PX_CODE,
Count([Procedure (Px)].PX_CODE) AS CodeCount, [Procedure
Reference].PX_TITLE
FROM [Procedure Reference] INNER JOIN [Procedure (Px)] ON [Procedure
Reference].PX_CODE = [Procedure (Px)].PX_CODE
GROUP BY [Procedure (Px)].PX_SURGEON, [Procedure (Px)].PX_CODE,
[Procedure Reference].PX_TITLE
ORDER BY [Procedure (Px)].PX_SURGEON, Count([Procedure (Px)].PX_CODE)
DESC;

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

That would be top 5 CodeCount per surgeon.

SELECT P.PX_SURGEON, P.PX_CODE, Count(*) AS CodeCount
FROM [Procedure (Px)] As P

WHERE PX_CODE In
( SELECT TOP 5 PX_CODE
FROM [Procedure (Px)]
WHERE PX_SURGEON = P.PX_SURGEON
GROUP BY PX_CODE
ORDER BY COUNT(*) DESC
)

GROUP BY P.PX_SURGEON, P.PX_CODE
ORDER BY 1, 3 DESC

The WHERE clause gets the top 5 PX_CODEs per surgeon. For the final
result the Count(*) As CodeCount in the main query re-counts the
PX_Codes found in the WHERE clause.

If there are ties in the WHERE clause TOP 5 the result set will show
more than 5 lines per surgeon. See the Access help article on the TOP
predicate.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5hXAoechKqOuFEgEQJUCwCg+Mrd2zQu344SzbRW4fLqBw nLxYEAn2G1
abHNQYkPtz+hgH0ZjuHe5p4N
=svBv
-----END PGP SIGNATURE-----
Kenny G wrote:

The current query gives me the total code count in desc order. I need
the top five codes for each surgeon with surgeon number in desc order.
It should look similar to this:

Surgeon PXCode CodeCount PXTitle
100 4013 3217 XXXXXXXX
100 3745 2866 XXXXXXXX
100 913 2313 XXXXXXX
100 1212 1819 XXXXXXX
100 7887 614 XXXXXXX

86 4550 5788 XXXXXX
86 377 3712 XXXXX
86 622 1712 XXXXXXXXX
86 1976 677 XXXXXXX
86 6650 76 XXXXX

I appreciate your help.

Kenny G

MGFoster <[e-mail]> wrote in message news:<nV****************@newsread3.news.pas.earthl ink.net>...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not clear what you want. Do you want the top 5 rows of your query or
a result set with only the top 5 Px_Codes (how would you define the
top 5 PX_Codes - highest count)? Or, do you want the top 5 PX_Codes
for each surgeon? Might be a good idea if you gave an example of what
you expect the result set would look like.

If top 5 of your query just change the SELECT clause like this:

SELECT Top 5 [Procedure (Px)].PX_SURGEON, [Procedure (Px)].PX_CODE,
Count([Procedure (Px)].PX_CODE) AS CodeCount, [Procedure
Reference].PX_TITLE
... etc. ...

and change the ORDER BY clause like this:

ORDER BY 3 DESC

I prefer to use the column ordinal value in the ORDER BY clause rather
than "Count([Procedure (Px)].PX_CODE)," 'cuz it avoids confusion -
we're sorting by the values in column 3 rather than the results of the
function Count() - which really is the value of column 3. It's just
that I don't have to mentally parse the expression - all I have to do
is see that it will be sorted by the 3rd column.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5cK24echKqOuFEgEQL21gCePc/jf9pQfLQZnR49v7aL4METxk4AoJEc
UwOvWmPmhKOKJpJBUCP2+gKD
=/kro
-----END PGP SIGNATURE-----
Kenny G wrote:

Below is a query that I currently have. I need to produce a subquery
so that the top five of the CodeCount is returned. I appreciate your
help.

SELECT [Procedure (Px)].PX_SURGEON, [Procedure (Px)].PX_CODE,
Count([Procedure (Px)].PX_CODE) AS CodeCount, [Procedure
Reference].PX_TITLE
FROM [Procedure Reference] INNER JOIN [Procedure (Px)] ON [Procedure
Reference].PX_CODE = [Procedure (Px)].PX_CODE
GROUP BY [Procedure (Px)].PX_SURGEON, [Procedure (Px)].PX_CODE,
[Procedure Reference].PX_TITLE
ORDER BY [Procedure (Px)].PX_SURGEON, Count([Procedure (Px)].PX_CODE)
DESC;

[Kenny G]

Thanks - that did it. I appreciate your help.

Kenny G

MGFoster <[e-mail]> wrote in message news:<XF*****************@newsread4.news.pas.earth link.net>...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

That would be top 5 CodeCount per surgeon.

SELECT P.PX_SURGEON, P.PX_CODE, Count(*) AS CodeCount
FROM [Procedure (Px)] As P

WHERE PX_CODE In
( SELECT TOP 5 PX_CODE
FROM [Procedure (Px)]
WHERE PX_SURGEON = P.PX_SURGEON
GROUP BY PX_CODE
ORDER BY COUNT(*) DESC
)

GROUP BY P.PX_SURGEON, P.PX_CODE
ORDER BY 1, 3 DESC

The WHERE clause gets the top 5 PX_CODEs per surgeon. For the final
result the Count(*) As CodeCount in the main query re-counts the
PX_Codes found in the WHERE clause.

If there are ties in the WHERE clause TOP 5 the result set will show
more than 5 lines per surgeon. See the Access help article on the TOP
predicate.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5hXAoechKqOuFEgEQJUCwCg+Mrd2zQu344SzbRW4fLqBw nLxYEAn2G1
abHNQYkPtz+hgH0ZjuHe5p4N
=svBv
-----END PGP SIGNATURE-----
Kenny G wrote:

The current query gives me the total code count in desc order. I need
the top five codes for each surgeon with surgeon number in desc order.
It should look similar to this:

Surgeon PXCode CodeCount PXTitle
100 4013 3217 XXXXXXXX
100 3745 2866 XXXXXXXX
100 913 2313 XXXXXXX
100 1212 1819 XXXXXXX
100 7887 614 XXXXXXX

86 4550 5788 XXXXXX
86 377 3712 XXXXX
86 622 1712 XXXXXXXXX
86 1976 677 XXXXXXX
86 6650 76 XXXXX

I appreciate your help.

Kenny G

MGFoster <[e-mail]> wrote in message news:<nV****************@newsread3.news.pas.earthl ink.net>...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not clear what you want. Do you want the top 5 rows of your query or
a result set with only the top 5 Px_Codes (how would you define the
top 5 PX_Codes - highest count)? Or, do you want the top 5 PX_Codes
for each surgeon? Might be a good idea if you gave an example of what
you expect the result set would look like.

If top 5 of your query just change the SELECT clause like this:

SELECT Top 5 [Procedure (Px)].PX_SURGEON, [Procedure (Px)].PX_CODE,
Count([Procedure (Px)].PX_CODE) AS CodeCount, [Procedure
Reference].PX_TITLE
... etc. ...

and change the ORDER BY clause like this:

ORDER BY 3 DESC

I prefer to use the column ordinal value in the ORDER BY clause rather
than "Count([Procedure (Px)].PX_CODE)," 'cuz it avoids confusion -
we're sorting by the values in column 3 rather than the results of the
function Count() - which really is the value of column 3. It's just
that I don't have to mentally parse the expression - all I have to do
is see that it will be sorted by the 3rd column.

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP5cK24echKqOuFEgEQL21gCePc/jf9pQfLQZnR49v7aL4METxk4AoJEc
UwOvWmPmhKOKJpJBUCP2+gKD
=/kro
-----END PGP SIGNATURE-----
Kenny G wrote:
Below is a query that I currently have. I need to produce a subquery
so that the top five of the CodeCount is returned. I appreciate your
help.

SELECT [Procedure (Px)].PX_SURGEON, [Procedure (Px)].PX_CODE,
Count([Procedure (Px)].PX_CODE) AS CodeCount, [Procedure
Reference].PX_TITLE
FROM [Procedure Reference] INNER JOIN [Procedure (Px)] ON [Procedure
Reference].PX_CODE = [Procedure (Px)].PX_CODE
GROUP BY [Procedure (Px)].PX_SURGEON, [Procedure (Px)].PX_CODE,
[Procedure Reference].PX_TITLE
ORDER BY [Procedure (Px)].PX_SURGEON, Count([Procedure (Px)].PX_CODE)
DESC;