sql question

[RPOC]

I have 1 table that used PATid,DISPENESED date,Vamount as well as other fields. The ids can duplicate. I want to be able to find the greatest date per id and then show the amount associated with the date. I then have a second table that has id, dob,enroll date. I want to do a join between the two tables.
Via some experimenting and research I was able to get the first part to occur but when I do the join the amount from table one acquires a value from another date under the id.

the below is the code I created. As stated without the join the results are what I want after the join they are incorrect.
SELECT
MAX(M.HISTORY_CLIENT_DISPENSE. dispense_date),
M.HISTORY_CLIENT_DISPENSE.PATID,
M.HISTORY_CLIENT_DISPENSE.v_dispensed_amount,
M.HISTORY_CLIENT_DISPENSE. V_medication_type_code,
M.HISTORY_CLIENT_DISPENSE. V_medication_type_value,
M.HISTORY_CLIENT_DISPENSE. medication_date,
M.HISTORY_CLIENT_DISPENSE.link_to_rti_CLIENT,
M.HISTORY_CLIENT_DISPENSE.LINK_TO_SPILL_CLIENT,
M.view_epis_sum_cur_AM.PATID
FROM
M.HISTORY_CLIENT_DISPENSE JOIN
M.view_epis_sum_cur_AM
ON (M.HISTORY_CLIENT_DISPENSE.PATID=M.view_epis_sum_cur_AM.PATID)

WHERE
M.hiSTORY_CLIENT_DISPENSE.link_to_rti_CLIENT IS NULL AND
M.HISTORY_CLIENT_DISPENSE.LINK_TO_SPILL_CLIENT IS NULL

GROUP BY
M.HISTORY_CLIENT_DISPENSE.PATID

ORDER BY
M.HISTORY_CLIENT_DISPENSE.PATID,
M.HISTORY_CLIENT_DISPENSE. medication_date

 

[r937]

I want to be able to find the greatest date per id and then show the amount associated with the date.

let's start with this part of the problem first

select foo.dispense_date
     , foo.PATID
     , foo.v_dispensed_amount
     , foo.V_medication_type_code
     , foo.V_medication_type_value
     , foo.medication_date
  from M.HISTORY_CLIENT_DISPENSE  as foo
 where foo.dispense_date
     = ( 
       select max(dispense_date)
         from M.HISTORY_CLIENT_DISPENSE
        where PATID = foo.PATID 
       )
order 
    by foo.PATID
     , foo. medication_date

note no GROUP BY -- this query uses a correlated subquery which in effect accomplishes the desired grouping

(your GROUP BY was wrong, as it did not include all the non-aggregate columns -- okay, mysql would run it, but it was wrong, especially for this forum )

let me know if the above query works for you

r937.com | rudy.ca