monetdb sql method to locate or match by the nearest value, without a TOP or LIMIT

Question

i am trying to replicate this question in monetdb, which i don't believe supports the TOP command and can only use LIMIT on the outermost result.

if i could use TOP then i believe this command would give me what i want. is there a reasonable alternative that's not massively inefficient? i need to run this on a table that will quickly max out my server's ram. thanks!

CREATE TABLE nearest_matches AS 
    ( SELECT a.* ,  
        (
            SELECT TOP 1 svcmon
            FROM person_table AS z
            WHERE a.yr = z.yr AND a.person_id = z.person_id
            ORDER BY abs( z.svcmon - a.svcmon )
        ) AS nearest_month
    FROM event_table AS a ) WITH DATA 

Answer 1

from Stefan Manegold at CWI

Hi,

making up my suggestions given my understanding of the desired semantics:

for the orignal question:

create table a (id int, sales int, date timestamp);
create table b (id int, goal int, date timestamp);
select a.*, b.* from a,b, (select a.date as a_date, max(b.date) as b_date from a,b where b.date < a.date group by a.date) as ab where a.date = ab.a_date and b.date = ab.b_date;

for the question below:

create table event_table (yr int, person_id int, svcmon int, payload string);
create table person_table (yr int, person_id int, svcmon int, payload string);
select * from (select e.yr, e.person_id, e.svcmon as e_svcmon, e.payload as e_payload, p.svcmon as p_svcmon, p.payload as p_payload, row_number() over (partition by e.yr,e.person_id order by abs(e.svcmon - p.svcmon) asc) as pos from event_table e , person_table p where e.yr = p.yr and e.person_id = p.person_id) as ep where pos = 1;

knowing actual schemas would help to understand whether "each event" is identified by yr,person_id (as I assume above) or by, say, (yr,person_id,svcmon) (in which case e.svcmon should be added to the partition-by clause).

knowing actual schemas might also help to pull the projection out of the inner query, as thus shrinking the intermediate result size(s) ...

Best, Stefan