Retrieve all distinct records from table and if any changes happen between two similar distinct record then need to consider both. Using select query

Question

I want to convert table1 into table2. As I need to find out all distinct records excluding mis_date fro the table and most important condition is if any changes happen between two similar distinct records than in that case I want both of them as two distinct records.

Example:

i/p

empId Empname Pancard MisDate 
123 alex ads234 31/11/2012 
123 alex ads234 31/12/2012 
123 alex ads234 31/01/2013 
123 alex dds124 29/02/2013 
123 alex ads234 31/03/2013 
123 alex ads234 31/04/2013 
123 alex dds124 30/05/2013 

Expected o/p

empId Empname Pancard MisDate 
123 alex ads234 31/11/2012 
123 alex dds124 29/02/2013 
123 alex ads234 31/03/2013 
123 alex dds124 30/05/2013 

Answer 1

Assuming there's only one row for each MisDate (otherwise you'll have to find another way to specify ordering):

SELECT t1.empId, t1.Empname, t1.Pancard
FROM Table1 t1
LEFT OUTER JOIN Table1 t2 
    ON t2.MisDate = (SELECT MAX(MisDate) FROM Table1 t3 WHERE t3.MisDate < t1.MisDate)
WHERE t2.empId IS NULL 
OR t2.empId <> t1.empId OR t2.Empname <> t1.Empname OR t2.Pancard <> t1.Pancard

SQL Fiddle example

This performs a self-join on the previous record, as ordered by MisDate, outputting if it is different or if there is no previous record (it is the first row).

Note: You've got some funky dates. I assume these are just transcription errors and have corrected them in the fiddle.