SQL Query returning same value while using where 1 condition

I have created a temp table and inserted the values as given below.

create table #temp( val int );

insert into #temp values(333);
insert into #temp values(222);
insert into #temp values(111);


On querying the below select statement I got 333 as the answer.

`Select *
from #temp a
Where 1 =(
Select COUNT(VAL)
from #temp b
where a.val

Result:

val

333

Can you please help me understand how SQL server came to this solution.

Rewrite your query such a way:

SELECT a.*, x.cnt
FROM #temp a
CROSS APPLY (
    SELECT COUNT(VAL) AS cnt
    FROM #temp b
    WHERE a.val <= b.val
) x
--WHERE x.cnt = 1

If you uncomment the where clause you would get 333 | 1 as a result. You request a row from the outer table which doesn't have duplicates or bigger values.

StackExchange Database Administrators Q#257844, answer score: 6