Can SQLite3 SELECT (up to) some rows based on a WHERE, and in the same query SELECT other rows on another condition?

Question

Hi it's my first time using SQLite and I am trying to understand if it can count the results it gets and switch conditions to find other results: I am not sure how to write this, so I will use an example:

From a table of players, with the same query I'd like to:

SELECT (x) players WHERE WINS>0 AND WINS<= 3 and 
       (y) other players WHERE WINS = Null OR WINS=0.

x and y should be integer numbers, but they could vary.

I think I can split the query in 2 queries, but I am worried about the performance since in this way I have to connect to the db twice, and I have to check on the second query that the new IDs have not been selected already in the first one (this should never happen in this example scenario, but might happen with different conditions).

If it's possible to write this all in one single query, that would be much more "simple" and straightforward.

Unfortunately I have to stick to SQLite 3.7.17 and I can't make use of all the updates until now.

mytable is defined like this one:

CREATE TABLE mytable (
    ID      TEXT    PRIMARY KEY,
    NAME TEXT,
    WINS INTEGER,
    LOSSES INTEGER,
    NOTES   TEXT
);

These are some dummy values:

INSERT INTO mytable(ID, NAME, WINS,LOSSES, NOTES) VALUES
('A001','John','1','0','blue')
('A002','Mark','2','1','blue')
('A003','Hubert','null','null','red')
('A004','Otto','0','0','green')
('A005','Johnson','3','5','red')
('A006','Frank','null','1','green')

As an example result the query should return: #first part (WINS>0 AND WINS<=3) x = 3

('A005','Johnson','3','5','red')
('A001','John','1','0','blue')
('A002','Mark','2','1','blue')

#second part(WINS=0 OR WINS=Null) y = 2

('A006','Frank','null','1','green')
('A004','Otto','0','0','green')

..so the result should be a table like that

('A005','Johnson','3','5','red')
('A001','John','1','0','blue')
('A002','Mark','2','1','blue')
('A006','Frank','null','1','green')
('A004','Otto','0','0','green')

Thank you for your time and knowledge. :)

Answer 1

Use a CTE where you filter the table for the conditions that you want to apply and with a CASE expression get an integer for the condition that is satisfied for each row.
In another CTE use ROW_NUMBER() window function to rank the rows of each condition.
Finally filter the rows with a CASE expression:

WITH 
  cond AS (
    SELECT *,
           CASE
             WHEN WINS > 0 AND WINS <= 3 THEN 1
             WHEN WINS IS NULL OR WINS = 0 THEN 2 -- or WHEN COALESCE(WINS, 0) = 0 THEN 2
           END AS condition
    FROM mytable
    WHERE condition IN (1, 2)
  ),
  cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY condition ORDER BY condition, WINS) rn FROM cond)
SELECT ID, NAME, WINS, LOSSES, NOTES
FROM cte
WHERE rn <= CASE condition 
        WHEN 1 THEN 3 -- x = 3
        WHEN 2 THEN 2 -- y = 2
      END
ORDER BY condition, WINS DESC;

See the demo.