how to write query for this output?

Posted by Vivekjj under Sql Server on 2/19/2013 | Points: 10 | Views : 1952 | Status : [Member] | Replies : 5

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

i have a table like this

empid      status      remarks

101            Y                    test
102            Y                    test1
101            N                    am
103            N                     hi
101            Y                     hai

i need output like this

empid      status      remarks

101            Y                    test
102            Y                    test1
103            N                     hi

that mean my 101 has duplicate values but its status oly Y i need to

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Rimi1289 on: 2/19/2013 [Member] Starter | Points: 25

0	Try the below query. SELECT * FROM ( SELECT , rn = ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY CODE) FROM emp ) Emp1 WHERE Emp1.rn = 1 Mark as answer, if its correct. Rimi @ http://www.encodedna.com Vivekjj, if this helps please login to Mark As Answer*. \| Alert Moderator

Posted by: Blessyjees on: 2/19/2013 [Member] Bronze | Points: 25

0	Hi, You table has two rows with status "y" for 101. do you need to only the firs y status value? Blessy Baby Digitalmesh Softech pvt Ltd https://blessybaby.wordpress.com/ Vivekjj, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sireesha on: 2/19/2013 [Member] Starter | Points: 25

0	select Distinct(empid),name,remarks from emp where remarks='test' union select * from emp where empid='102' or empid='103' try this A.Sireesha Vivekjj, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Oswaldlily on: 2/20/2013 [Member] Starter | Points: 25

0	select Distinct(id),stat,remarks from tablename where remarks='test' Vivekjj, if this helps please login to Mark As Answer. \| Alert Moderator

Posted by: Sriramnandha on: 5/12/2013 [Member] Starter | Points: 25

0	WITH DUPLICATE(ID, RowNumber) AS ( SELECT ID ,ROW_NUMBER() OVER (PARTITION BY ID order by ID) AS RowNumber FROM MYTABLE ) Select * FROM DUPLICATE WHERE RowNumber = 1 -- This seems bit overkill for question asked but if we want to DELETE the DUPLICATE ROW(s) then: WITH DUPLICATE(ID, RowNumber) AS ( SELECT ID ,ROW_NUMBER() OVER (PARTITION BY ID order by ID) AS RowNumber FROM MYTABLE ) DELETE FROM DUPLICATE WHERE RowNumber >1 sriram Vivekjj, if this helps please login to Mark As Answer. \| Alert Moderator

WITH DUPLICATE(ID, RowNumber)
AS
(
SELECT ID
,ROW_NUMBER() OVER (PARTITION BY ID order by ID) AS RowNumber
FROM MYTABLE
)
Select * FROM DUPLICATE WHERE RowNumber = 1

-- This seems bit overkill for question asked but if we want to DELETE the DUPLICATE ROW(s) then:

WITH DUPLICATE(ID, RowNumber)
AS
(
SELECT ID
,ROW_NUMBER() OVER (PARTITION BY ID order by ID) AS RowNumber
FROM MYTABLE
)
DELETE FROM DUPLICATE WHERE RowNumber >1

sriram

Vivekjj, if this helps please login to Mark As Answer. | Alert Moderator

Latest Posts