How to use order by before group by in SQLite?

Question

I want to combine information from two data table, t1 and t2

t1:

ID TIME_ADD
 1 1620838960308
 2 1620879991077
 3 1620927396290
 

t2:

ID TIME_START    TIME_END      LEVEL
 1 1620837000611 1620840600621 0
 2 1620837000611 1620846000620 1
 3 1620837000611 1620851400622 0
 4 1620837000611 1620879262116 3
 5 1620837000611 1620881062117 2
 6 1620837000611 1620882862117 0
 7 1620923400574 1620923400577 2
 8 1620923400574 1620930600578 1
 ...

therefore, I have the query as following:

SELECT t1.*, t2.LEVEL 
FROM t1 
INNER JOIN t2 
ON t2.TIME_START < t1.TIME_ADD AND t2.TIME_END >= t1.TIME_ADD 
WHERE t1.TIME_ADD >= '1620837000611' AND t1.TIME_ADD <= '1620882862117'
ORDER BY t1.TIME_ADD
GROUP BY t1.TIME_ADD

And expected result to be

ID TIME_ADD      LEVEL
 1 1620838960308 0
 2 1620879991077 2

However, the query above does not work, and it seems to be that I'm using ORDER BY and GROUP BY wrongly. Spent quite some time to try to find out a way, also looking into using INNER JOIN with LIMIT, but without success. So any support here would be highly appreciated.

Answer 1

The ORDER BY clause comes after the GROUP BY clause.

If you want 1 row for each distinct TIME_ADD of t1 with the LEVEL of the row with the min ID of t2, you can do it with HAVING MIN(t2.ID), which is a feature of SQLite (assuming there is no ID in t2 with a 0 value):

SELECT t1.*, t2.LEVEL 
FROM t1 
INNER JOIN t2 
ON t2.TIME_START < t1.TIME_ADD AND t2.TIME_END >= t1.TIME_ADD 
WHERE t1.TIME_ADD >= '1620837000611' AND t1.TIME_ADD <= '1620882862117'
GROUP BY t1.TIME_ADD
HAVING MIN(t2.ID)
ORDER BY t1.TIME_ADD;

See the demo.