I've a table with data. The rows are repeated but the change is in only one column(status). Let the status column data be (A, B, C).
Sometimes data will be available with status A, B and C . Then 3 rows will be created for one record(ID=2001). What I need is, If A,B,C are available, the query should return only the row where Status=' A'. Sometimes for another record (ID=2005) B &C will be status. Then I should get row Where Status='B'
How can i tackle this situation with SQLQuery? I'm using SQLServer2008. Any responses will be appreciated.
SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY [STATUS]) AS RNO, *
FROM <yourtable> /*where ID =2001 AND[STATUS] IN ('A','B','C')*/
WHERE RNO =1
select ID, min(Status) as Status
group by ID
order by ID
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)