SELECTING record based on condition

Sorry your explanation doesnt match the given output. I can see three records for ABC on 03/03/2015 with two of them having state as ON. Then why did in output it came with State as OFF?

As per below

but if in case more than 1 record is present for the same name on the same date then check the state if 'ON' is there then simply select one record with the 'ON' state

I would expect state to be ON for ABC on 03/03/2015

SELECT Name,NameID,DataID,[Date],[State]
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY Name,NameID,[Date] ORDER BY CASE WHEN [State] = 'ON' THEN 1 ELSE 2 END) AS Rn,*
FROM [State]
)t
WHERE rn = 1

---

Please Mark This As Answer if it solved your issue

Please Vote This As Helpful if it helps to solve your issue

Visakh

----------------------------

My Wiki User Page

My MSDN Page

My Personal Blog

My Facebook Page