SQL Query returning same value while using where 1 condition
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.