On Mon, 05 May 2008 10:01:43 +0200, Sandy80 <svarshneymail@gmail.com>
wrote:

> Hi,
>
> The table that I have has a sample records like below:
>
> Emp No. Unique ID From Date To Date
> 11 1234 01-Jan-2005 30-Sep-2005
> 11 2345 01-Oct-2005 31-Oct-2005
> 11 3456 01-Nov-2005 31-Oct-2005

Having real date columns would have saved you a lot of headaches...
This will simply not do:
mysql> select date('01-Oct-2007');
+---------------------+
| date('01-Oct-2007') |
+---------------------+
| NULL |
+---------------------+
1 row in set, 1 warning (0.00 sec)

And compare 'from date' to 'to date' will just do a string comparison
instead of time. I suggest using any script you are familiar with to alter
the dates to something mysql is comfortable with (yyyy-mm-yy), and
changing the columns to date columns. Offcourse, if your data is reliable,
you could run 12 REPLACE()s to get the dates to a numeric format, and
shuffle the values around with CONCAT() & SUBSTR().

> The task that I have is to find out records where the From Date is
> greater than the To Date. This is needed because the 3rd record in the
> example above is actually an erroneous record which needs to be
> deleted. The query that I was able to write was returning the 2nd
> record also because 01-Oct-2005 is greater than 30-Sep-2005. Please
> help me with the query which would return only the 3rd record.

After you've done that, its a simple matter of:
SELECT `Unique ID` FROM tablename WHERE `From Date` < `To Date`;


Altering the database/table in this way may be a lot of work at first,
depending how and how much other code uses it, but will have major
benefits in the end.
--
Rik Wasmus