Compare each field in the table with all fields in one table

Question

Imagine a single column table.

+------+
|  v   |
+------+
|0.1234|
|0.8923|
|0.5221|
+------+

I want to do the following for line K:

• Take the string K = 1 value: 0.1234
• Indicate how many values ​​in the rest of the table are less than or equal to the value in row 1.

Iterate over all lines. The output should be:

+------+-------+
| v    |output |
+------+-------+
|0.1234|   0   |
|0.8923|   2   | 
|0.5221|   1   | 
+------+-------+

Quick Update I used this approach to compute statistics for each v value in the table above. The cross-join approach was too slow for the size of the data I was dealing with. So, instead, I calculated my stat for a grid of v values, and then matched them with vs in the source data. v_table is the data table from before, and stat_comp is the statistics table.

AS SELECT t1.* 
,CASE WHEN v<=1.000000 THEN pr_1 
WHEN v<=2.000000 AND v>1.000000 THEN pr_2 
FROM v_table  AS t1 
LEFT OUTER JOIN stat_comp AS t2

Answer 1

select 0.1234 as v into #t
union all
select 0.8923
union all
select 0.5221

;with ct as (
    select ROW_NUMBER() over (order by v) rn
    , v
    from #t ot
)
select distinct v, a.cnt
from ct ot
    outer apply (select count(*) cnt from ct where ct.rn <> ot.rn and v <= ot.v) a

Answer 2

Windows ANSI/ISO SQL 1999 Hive 0.11, 15 2013 .
, , - , ANSI/ISO SQL: 2011 :

rank () over (order by v with ties high) - 1

with ties ..., count(*) over (...)

---

select  v
       ,count(*) over (order by v) - 1 as rank_with_ties_high_implicit

from    mytable
;

select  v
       ,count(*) over 
        (
            order by v
            range between unbounded preceding and current row            
        )  - 1  as rank_with_ties_high_explicit

from    mytable
;

Answer 3

, , .. CROSS JOIN . foo , bar:

SELECT foo.v, COUNT(foo.v) - 1 AS output
FROM foo
CROSS JOIN foo bar
WHERE foo.v >= bar.v
GROUP BY foo.v;

.

, ( , SUM GROUP BY bar.v SELECT). , foo.v >= bar.v, .

Answer 4

case:

select a.x
, sum(case when b.x < a.x then 1 else 0 end) as count_less_than_x
from (select distinct x from T) a
, T b
group by a.x

, .

Note that there is no join condition or where clause. In this case, we really want this. For each line, awe get a full copy with an alias like b. Then we can check each one to see less or less than a.x. If so, add 1 to the score. If not, we just add 0.