How to pick tje last record for particular group with particular condition.
Posted on 2010-09-11
Table name - Txnenrollment
Fields - enrollmentdate,villagecode,familyid,maincardserialno,enrolled and personalized
Table name - Villagemaster
Created with this manner
select * from txnenrollment where enrolled=1
order by enrollmentdate,villagecode,maincardserialno desc
If enrolled=1 and personalized=1 then maincardserialno is not blank
if enrolled=1 and personalized=0 then maincardserialno is blank
Suppose for enrollmentdate - d1, villagdecode - v2, there are 4 records
out of which record 1 and 2 have conditions as enrolled=1 and personalized=1 that means maincardserialno is filled.
And 3 and 4 record satisfy the conditon of enrolled=1 and personalized=0.
I want to fill that record the maincardserialno with the maincardserialno of record2 +17 by checking that new maincardserialno generated record 3 is unique in whole table.
if not then add 1 more 17 to maincardserialno and update record3.
This way the question is how to pick the last record.