How to pick tje last record for particular group with particular condition.

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.

Reading this and your other question regarding making the serial no unique, I am suspecting that you should use the first method presented by matthewspatrick which is to have a derived table with the MAX; however, it would appear that you need this on the serial no field and not the date field. Furthermore, you would not include this max in the JOIN but instead simply use its value in selection if the serial no of current row is NULL or Blank.

An alternative, would be to use sub query only on the rows that had NULL or Blank via a similar case statement.

It will probably be good to know if the actual records are NULL or '' (empty or blank string).

Your case statement could look something like:

case when maincardserialno is null
then -- our calculation here
else maincardserialno -- leave serial no in place
end

Here is a technique for adding 17 to the serial number without having to do on last 2 characters only and having to worry about going above 100.

by Mark Wills
PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…

In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…