Query Problem

abe6162 · #1 (**permalink**) 06-11-04, 11:18

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 · #2 (**permalink**) 06-11-04, 22:24

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

abe6162 · #3 (**permalink**) 06-12-04, 10:23

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 · #4 (**permalink**) 06-12-04, 18:38

"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 · #5 (**permalink**) 06-13-04, 00:16

Consider the following example.

Code:

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

Internet Services

Web Development

Digital Marketing

Consumer Tech