i have 2 tables, machinewisematerial (fields: mcmatid) , materialissueregister (fields: mcmatid, issuedate, issuetime, emr). want last issue date, time , emr each mcmatid.
i can find same individual mcmatid using select top 1 follows.
select top 1 mcmatid, issuedate, issuetime, emr materialissueregister mcmatid = [some value, eg. 1] order issuedate desc, issuetime desc, emr desc; but when i'm trying join both tables, i'm not getting desired results.
select machinewisematerial.mcmatid, b.issuedate, b.emr machinewisematerial left join (select top 1 mcmatid, issuedate, issuetime, emr materialissueregister order issuedate desc, issuetime desc, emr desc) b on machinewisematerial.mcmatid = b.mcmatid; the above query returns mcmatid issuedate max , not max date each mcmatid.
you try this..
select machinewisematerial.mcmatid, b.issuedate, b.emr machinewisematerial m1 m1.mcmatid=(select top 1 mcmatid materialissueregister m2 m2.mcmatid=m1.mcmatid order issuedate desc, issuetime desc, emr desc) 