Thanks John. I posted the test data similar to what we got in Hive results(test data is coming like this may be due to hive nature as there is no update concept in table and only insert is available in the particular project) .Hadoop-Hive is using Map reduce program and it is very slow. Thorn framed a  query which was fast and it almost worked.
I will check with the query you  developed with my test data and get back to you .

Regards,
Saravanan