how to write query for this output?

Posted by Vivekjj under Sql Server on 2/19/2013 | Points: 10 | Views : 943 | Status : [Member] | Replies : 5
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




Responses

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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Up
0
Down
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

Login to post response