1. Registered User
Join Date
Feb 2004
Posts
24

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

2. Registered User
Join Date
Sep 2003
Location
The extremely Royal borough of Kensington, London
Posts
778
select *
from tableA ta
where pres_num = (select max(presnum) from tableA where territory_num = ta.territory_num)

3. Registered User
Join Date
Feb 2004
Posts
24
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.

Thanks so much for helping!

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002

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

5. Registered User
Join Date
Sep 2003
Location
The extremely Royal borough of Kensington, London
Posts
778
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```

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•