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 &lt;= b.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 &lt;= 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.