SQL Server Query – Why Same Value Returned with WHERE 1 Condition

querysql server

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 <= b.val
);

Result:

val

333

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

Best Answer

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.

Related Question