Select distinct record [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	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


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

0	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

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

0	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

0	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

Latest Posts