How to get Top 1 with below statement

Question

Hi

In below statement i want to get Top 1 record

CASE
    WHEN (
        SELECT COUNT(*)
        FROM SessionDeliveryParticipant AS sub
        WHERE sub.[SessionDeliveryCalendarID] = [SessionDeliveryCalendarID]
        AND sub.PresentInSession = 0
    ) = COUNT(*) THEN 1
    ELSE 0 End

Thanks

Tuhin Paul · Answer

SELECT TOP 1 [Attendance] FROM SessionDeliveryParticipant sdp WHERE EXISTS ( SELECT 1 FROM SessionDeliveryParticipant sub WHERE sub.[SessionDeliveryCalendarID] = sdp.[SessionDeliveryCalendarID] GROUP BY sub.[SessionDeliveryCalendarID] HAVING COUNT(*) = SUM(CASE WHEN sub.PresentInSession = 0 THEN 1 ELSE 0 END) ) I used the EXISTS keyword to check for the existence of records in the subquery result. The subquery groups the records by SessionDeliveryCalendarID and calculates the count of records where PresentInSession is 0. Then, it compares this count with the total count for each SessionDeliveryCalendarID. If they match, it means all the participants for that calendar session have PresentInSession set to 0.

Amit Mohanty · Answer

Try this SELECT TOP 1 [Attendance] FROM SessionDeliveryParticipant WHERE [Attendance] = ( SELECT CASE WHEN ( SELECT COUNT(*) FROM SessionDeliveryParticipant AS sub WHERE sub.[SessionDeliveryCalendarID] = SessionDeliveryParticipant.[SessionDeliveryCalendarID] AND sub.PresentInSession = 0 ) = COUNT(*) THEN 1 ELSE 0 END FROM SessionDeliveryParticipant )

Ramco Ramco · Answer

Hi Amit I have written below statement but on Case it is giving message - An expression of non boolean type specified in a context where condition is expected. ,(SELECT TOP 1 [Attendance] FROM SessionDeliveryParticipant where CASE WHEN ( SELECT COUNT(*) FROM SessionDeliveryParticipant AS sub WHERE sub.[SessionDeliveryCalendarID] = [SessionDeliveryCalendarID] AND sub.PresentInSession = 0 ) = COUNT(*) THEN 1 ELSE 0 End) [Attendance] Thanks

Amit Mohanty · Answer

SELECT TOP 1 * FROM YourTable WHERE CASE WHEN ( SELECT COUNT(*) FROM SessionDeliveryParticipant AS sub WHERE sub.[SessionDeliveryCalendarID] = YourTable.[SessionDeliveryCalendarID] AND sub.PresentInSession = 0 ) = COUNT(*) THEN 1 ELSE 0 END = 1;