Is there a way to select the first match according to some logic when order by limit 1 isn't applicable?

(1) By anonymous on 2026-06-01 04:17:42 [link] [source]

I'm trying to compare selection data in a previously captured and now "stale" UI view to current state data and apply logic to make a new selection when one is available. This example appears to provide the correct answers but I'm posting to ask if there is a better way to apply it in SQLite and if it would be better to just run separate queries in application code when the previous query returns empty; because this example finds all three options when they exist and selects the "best" one and separate queries would stop at the first match.

Table "data" is the current state where r is the row position. The "selnode" in the CTE is a list of example stale selections; that is, what the selection used to be but which has now become stale due to user interaction.

I'd like to apply logic as follows:

If the selnode selection's id is still in the current data, select that row even if the node does not match.
If 1 is false and the selnode selection's node is in current data, select that row regardless of its id.
If 1 and 2 are false, then select the row closest to the selnode selection's r value.

Thank you.

drop table if exists data;
create table data (r integer, node text, id integer)
;
insert into data values 
  (1,'a',4),
  (2,'b',8),
  (3,'c',3),
  (4,'d',1),
  (5,'e',5)
;

with
selnode(ex_id,r,node,id) as
( values
 (1,4,'d',4),
 (2,1,'a',1),
 (3,2,'b',2),
 (4,8,'h',12),
 (5,2,'f',14)
),
step_1 as
(
 select 
    s.ex_id,
    iif(
        d.id = s.id,1,
        d.node = s.node,2,
        d.r = min(s.r,(select max(r) from data)),
        0
    ) as match,
    d.*
 from 
    data d inner join selnode s on
        d.id = s.id
     or d.node = s.node
     or d.r = min(s.r,(select max(r) from data))
)
select * from step_1
group by ex_id 
having match=min(match);

/*
╭───────┬───────┬───┬──────┬────╮
│ ex_id │ match │ r │ node │ id │
╞═══════╪═══════╪═══╪══════╪════╡
│     1 │     1 │ 1 │ a    │  4 │ Id matches, node different
│     2 │     1 │ 4 │ d    │  1 │ Id matches, node different
│     3 │     2 │ 2 │ b    │  8 │ Matching node, different id
│     4 │     0 │ 5 │ e    │  5 │ Id and node have no match, closest is row 5.
│     5 │     0 │ 2 │ b    │  8 │ Id and node have no match, closest is row 2.
╰───────┴───────┴───┴──────┴────╯
*/

(2) By ralf (ralfbertling) on 2026-06-01 12:09:17 in reply to 1 [link] [source]

Hi, 
I fail to understand what you mean by LIMIT is not applicable.
If you refer to a syntactic problem, you can always define another cte-part like this:

...
     or d.r = min(s.r,(select max(r) from data))
), prelim as (
  select * from step_1
   group by ex_id 
  having match=min(match)
)
select * from prelim order by r limit 1

Hope this helps, ralf

(3) By anonymous on 2026-06-01 19:15:20 in reply to 2 [source]

Thanks. Bad/confusing title; it has nothing to do with limit; I meant that I want to apply logic that is beyond an order by limit 1. I was referring to "first match" in terms of the three conditions listed. In the example, up to all three conditions may apply and then the preferred among them is selected. I wondered if there was a way in SQL to perform the equivalent of stop testing once the first match is found. Sort of like what is attempted in the recursive query below by trying to join in some heirarchy and not join again if a previous join was true. I mean, since the order of conditions in a WHERE clause is not necessarily the order evaluated, SQLite may never join a previous match on table "data" again since "easier" test evaluate to false. But this may be convoluted, though it appears to provide the correct results. Thanks.

with recursive
selnode(ex_id,r,node,id,status) as
( values
 (1,4,'d',4,-1),
 (2,1,'a',1,-1),
 (3,2,'b',2,-1),
 (4,8,'h',12,-1),
 (5,2,'f',14,-1)
),
rec(ex_id,r,node,id,status,test,prevtest) as 
(
select *,1,0 from selnode
UNION ALL
 select
     s.ex_id,
     ifnull(d.r,s.r),
     ifnull(d.node,s.node),
     ifnull(d.id,s.id),
     iif(d.id is null,s.status,s.test),
     s.test+1,
     s.test
 from 
    rec s left join data d on
        (s.status,s.test) = (-1,s.prevtest+1)
        and 
        (  s.test = 1 and d.id = s.id
        or s.test = 2 and d.node=s.node
        or s.test = 3 and d.r = min(s.r,(select max(r) from data))
        ) 
where test < 4
)
select * from rec where test = 4;

/*
╭───────┬───┬──────┬────┬────────┬──────┬──────────╮
│ ex_id │ r │ node │ id │ status │ test │ prevtest │
╞═══════╪═══╪══════╪════╪════════╪══════╪══════════╡
│     1 │ 1 │ a    │  4 │      1 │    4 │        3 │
│     2 │ 4 │ d    │  1 │      1 │    4 │        3 │
│     3 │ 2 │ b    │  8 │      2 │    4 │        3 │
│     4 │ 5 │ e    │  5 │      3 │    4 │        3 │
│     5 │ 2 │ b    │  8 │      3 │    4 │        3 │
╰───────┴───┴──────┴────┴────────┴──────┴──────────╯
*/