I need to have a SQL that finds values from table B using (randomize) values on table A in comparative manner. Table A values has been produces in randomize manner. Table B values have been order in a way of cumulative distribution function. What is needed is that SQL will get the first row from table B which satisfy the criteria.
Table A: +----+-------+ | ID | value | +----+-------+ | 1 | 0.1234| | 2 | 0.8923| | 3 | 0.5221| +----+-------+ Table B: +----+-------+------+ | ID | value | name | +----+-------+------+ | 1 | 0.2000| Alpha| | 2 | 0.5000| Beta | | 3 | 0.7500| Gamma| | 4 | 1.0000| Delta| +----+-------+------+ Result should be: +----+-------+------+ | ID | value | name | +----+-------+------+ | 1 | 0.1234| Alpha| | 2 | 0.8923| Delta| | 3 | 0.5221| Gamma| +----+-------+------+
Value 0.1234 is smaller than all the values of B, but Alpha has smallest value.
Value 0.8923 is smaller than 1.000 --> Delta.
Value 0.5221 is smaller than both 0.7500 and 1.000 but 0.7500 is smallest --> Gamma.
This query works only if table A has one value:
select value, name from B where (select value from A) < value;
Any ideas how to get this work with full table A?
You can use subquery to get the data you need:
SELECT a.ID, a.value,
(SELECT b.name FROM TableB b WHERE a.value < b.value ORDER BY b.ID ASC LIMIT 1) as name
FROM TableA a
In this case for each row in table A you find the first record in table B, that has larger number in column value. Depending on your requirements the operator < might beed to be updated to <= - it depends on your requirements