#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    8
    Rep Power
    0

    Trouble with MAX function


    I am having trouble retrieving the Max, latest date, from a table with a join to one another table and other fields from both.

    I was able to get the MAX service_date grouped by id. But once I tried to add more fields to the query and another table it won't work.

    Here is what I have:

    select MAX(cs.service_date), cs.notes, cs.applicant_id,wr.program_code,wr.last_name,wr.first_name,wr.region_code,wr.status_cd

    from cs join wr on cs.applicant_id=wr.applicant_id

    where wr.status_cd='AC'

    group by cs.applicant_id

    Help????
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Feb 2005
    Posts
    724
    Rep Power
    291
    The reason for this is because when you use Group By, you must include all select fields in the Group By.

    Below will run but it probably won't produce the results you are trying to achieve.

    Code:
    select	
    	MAX(cs.service_date), 
    	cs.notes, 
    	cs.applicant_id,
    	wr.program_code,
    	wr.last_name,
    	wr.first_name,
    	wr.region_code,
    	wr.status_cd
    from cs 
    join wr on cs.applicant_id=wr.applicant_id
    where wr.status_cd='AC' 
    group by cs.applicant_id,wr.program_code,wr.last_name,wr.first_name,wr.region_code,wr.status_cd
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    8
    Rep Power
    0
    I get an error message:

    ORA-00979: not a GROUP BY expression
    Last edited by Lori J; September 19th, 2011 at 02:51 PM. Reason: change error message
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    8
    Rep Power
    0
    I got it to work. Must be I had something wrong.

    Thanks!!!

IMN logo majestic logo threadwatch logo seochat tools logo