Results 1 to 5 of 5

Thread: Query Problem

  1. #1
    Join Date
    Feb 2004
    Posts
    24

    Unanswered: 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_num

  2. #2
    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)
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    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.

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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    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
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

Posting Permissions

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