sql query which want to retreve upto 3conditions
===

I have a data in the source table as mentioned below:

Employee_Id	Status	Date	Address	Phone number
52345	N	28-Jan-10	ABC	9833455456
52345	N	30-Apr-10	ABC	9833455456
52345	Y	13-Dec-10	ABC	9934467879
10145	N	25-Mar-10	PQR	8766129989
32456	N	20-Jun-10	XYZ	9860799849
32456	N	03-Nov-10	XYZ	9860799849
32456	Y	07-Feb-11	XYZ	9860799849
32456	Y	22-May-11	XYZ	8977767889
32456	N	18-Sep-11	DEF	9811223300
42123	N	25-Feb-10	MNO	9920030048
42123	N	23-Sep-11	MNO	9833445566

The objective is to get the unique record for an Employee in the output but based on below mentioned criteria:

1)If Employee Id is unique, then pass it as it is.
2)If Employee Id is duplicate, then pass the record where status=”Y”
3)If Employee Id is duplicate even after applying status=”Y”, then lastly pick the record having latest Date.

