Query Problem

I have a table with columns -- ims_num, last_name, pres_num, territory_num

I need retrieve the max pres_num grouped by territory

I got so far

select max(pres_num) from table
group by territory_num

I need to show all the data -- ims_num, last_name, pres_num, territory_numselect *
from tableA ta
where pres_num = (select max(presnum) from tableA where territory_num = ta.territory_num)|||I tired this, but it didn't work.

The problem is the the max(pres_num) of one territory can be the pres_num in another terriotory, and the pres_num might not be the max(pres_num) in that territory.

I hope that made sense.
Thanks so much for helping!|||"I hope that made sense."

nope, it didn't

it made things a lot worse

perhaps you could show some sample rows, and then an example of what the result set should be|||Consider the following example.

SQL> select * from a;

NAME DEPT AMT
---- ---- ----
EMPA DEPTA 80000
EMPB DEPTA 60000
EMPC DEPTB 80000
EMPD DEPTB 60000

SQL> select dept, max(amt)
2 from a
3 group by dept;

DEPT MAX(AMT)
---- ----
DEPTA 80000
DEPTB 80000

SQL> select *
2 from a a1
3 where a1.amt = (select max(amt) from a where dept = a1.dept);

NAME DEPT AMT
---- ---- ----
EMPA DEPTA 80000
EMPC DEPTB 80000

SQL> select a1.name, V.*
2 from a a1
3 INNER JOIN
4 (select dept, max(amt) MAX_AMT
5 from a
6 group by dept) V ON
7 V.dept = a1.dept AND
8 V.MAX_AMT = a1.amt;

NAME DEPT MAX_AMT
---- ---- ----
EMPA DEPTA 80000
EMPC DEPTB 80000