Select distinct record [Resolved]

Posted by Vinokanna under Sql Server on 2/20/2015 | Points: 10 | Views : 592 | Status : [Member] | Replies : 4
Hi,

id Sl.No status (status is last column)
11 314975 2
11 314975 3
11 314975 3


id Sl.No status
11 214974 3
11 214974 3
11 214974 3

this two records are in same table,
i want only below record slno 214974 here status is matter only 3 i need.
But when i query the above record also come because of 3 status in the record

Please help.
Vinoth




Responses

Posted by: Bandi on: 2/25/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved

SELECT ID,SlNo -- , COUNT(*) as TotalRecordsinEachGroup ,SUM(CASE WHEN Status = 3 then 1 else 0 END) CountofRecordsWithoutStatus3
FROM TableName
GROUP BY ID, SlNo
HAVING SUM(CASE WHEN Status = 3 then 1 else 0 END) >= COUNT(*)


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Posted by: Bandi on: 2/20/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Your explanation is lacking some other conditions...

How can you tell slno 214974 record should be the output?
Why NOT this slno 314975 ?

Post us back the explanation for the expected result so that we can provide you the SQL query


Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Posted by: Vinokanna on: 2/24/2015 [Member] Starter | Points: 25

Up
0
Down
Because i want only the record with status of 3
if a record status of 3 and 2 i want to eliminate from select

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

Posted by: Bandi on: 3/3/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
Mark it as answer

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

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

Login to post response