Maximu value along with all fields value

[imfarhan]

<TABLE cellSpacing=0 bgColor=#ffffff border=1><CAPTION>qryMax No of procedures</CAPTION><THEAD><TR><TH borderColor=#000000 bgColor=#c0c0c0>InternalNo</TH><TH borderColor=#000000 bgColor=#c0c0c0>EpisodeNo</TH><TH borderColor=#000000 bgColor=#c0c0c0>All_Proc</TH><TH borderColor=#000000 bgColor=#c0c0c0>CntProcedures</TH></TR></THEAD><TBODY><TR vAlign=top><TD borderColor=#c0c0c0>1234</TD><TD borderColor=#c0c0c0>7</TD><TD borderColor=#c0c0c0>W90.1 Z84.6 Z94.2</TD><TD borderColor=#c0c0c0 align=right>3</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>1235</TD><TD borderColor=#c0c0c0>1</TD><TD borderColor=#c0c0c0>B37.1 Z94.2</TD><TD borderColor=#c0c0c0 align=right>2</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>1236</TD><TD borderColor=#c0c0c0>5</TD><TD borderColor=#c0c0c0>W36.3 W36.5 Z75.3</TD><TD borderColor=#c0c0c0 align=right>3</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>1236</TD><TD borderColor=#c0c0c0>1</TD><TD borderColor=#c0c0c0>G45.9</TD><TD borderColor=#c0c0c0 align=right>1</TD></TR><TR vAlign=top><TD borderColor=#c0c0c0>1234</TD><TD borderColor=#c0c0c0>13</TD><TD borderColor=#c0c0c0>S06.9 Z50.4 Z94.3 Z50.1 Z94.2</TD><TD borderColor=#c0c0c0 align=right>5</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Hi I would like to pull only that row which keep maximum cntprocedure along with all fields.
I have tried following queries but no success

Select internalNo,EpisodeNo, All_proc,Max(CntProcedures)
from table
group by internalNo,EpisodeNo, All_proc

It pulls all rows with their grroup but I want pull only following row in this case?

<TABLE cellSpacing=0 bgColor=#ffffff border=1><TBODY><TR vAlign=top><TD borderColor=#c0c0c0>1234</TD><TD borderColor=#c0c0c0>13</TD><TD borderColor=#c0c0c0>S06.9 Z50.4 Z94.3 Z50.1 Z94.2</TD><TD borderColor=#c0c0c0 align=right>5</TD></TR></TBODY><TFOOT></TFOOT></TABLE>

Also tried but generate error
Select internalNo,EpisodeNo, All_proc, cntprocdeure
from table a
where exists (select max(cntprocdure) from table b
where a.internalno = b.internalno)

Thanks for your help

regards
F

 

[boblarson]

Here you go - change the table name of TableProcs to the name of your query which gives you the initial counts.

SELECT TableProcs.InternalNo, TableProcs.EpisodeNo, TableProcs.All_Proc, TableProcs.CntProcedures
FROM TableProcs
WHERE (((TableProcs.CntProcedures)=(Select Max([CntProcedures]) From TableProcs)));