[abe6162]

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_num

[r123456]

select *
from tableA ta
where pres_num = (select max(presnum) from tableA where territory_num = ta.territory_num)

[abe6162]

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!

[r937]

"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

[r123456]

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