4

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.

  • SQL server returned exactly what you requested. What is your expected result? – Denis Rubashkin Jan 21 at 10:00
  • @DenisRubashkin, Can you please explain how did query return 333? – l.lijith Jan 21 at 10:03
  • You ask server to find the record for which there exists only one record with the value of val equal or greater (so this 'only one' is this record itself) if it is alone (count=1, not duplicated). And you obtain strictly the record which matches this conditions. Why you're surprised? – Akina Jan 21 at 10:15
6

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.

| improve this answer | |
3

You could see your inner count query

Select COUNT(VAL) from #temp b  
where a.val <= b.val;

as

(Select COUNT(VAL) from #temp b  
where 333 <= 333,222,111) = 1


(Select COUNT(VAL) from #temp b  
where 222 <= 333,222,111) = 2


(Select COUNT(VAL) from #temp b  
where 111 <= 333,222,111) = 3

Showing that only 333 from #temp a has one match, as it has only one equals to or smaller than match with the 3 values in #temp b.

This returns a count of 1 and is why the value 333 from #temp a is returned.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.