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

i trying replicate this question in monetdb, don't believe supports top command , can use limit on outermost result.

if use top believe command give me want. there reasonable alternative that's not massively inefficient? need run on table max out server's ram. thanks!

create table nearest_matches      ( select a.* ,           (             select top 1 svcmon             person_table z             a.yr = z.yr , a.person_id = z.person_id             order abs( z.svcmon - a.svcmon )         ) nearest_month     event_table ) data  

from stefan manegold @ cwi

hi,

making suggestions given understanding of desired semantics:

for orignal question:

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

for 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 * (select e.yr, e.person_id, e.svcmon e_svcmon, e.payload e_payload, p.svcmon p_svcmon, p.payload p_payload, row_number() on (partition e.yr,e.person_id order abs(e.svcmon - p.svcmon) asc) pos event_table e , person_table p e.yr = p.yr , e.person_id = p.person_id) ep pos = 1; 

knowing actual schemas understand whether "each event" identified yr,person_id (as assume above) or by, say, (yr,person_id,svcmon) (in case e.svcmon should added partition-by clause).

knowing actual schemas might pull projection out of inner query, shrinking intermediate result size(s) ...

best, stefan