Sqlite - Query on table?

Question

I have a lot of records like this (over 6000) in this table Lat and Lng is same and I should select base these fields (lat,lng) :

enter image description here

I need to computing on these records ( enter image description here and enter image description here )

I need to get first record of a Lat Lng(same) and latest (between same date) for compute on time field with filter date ,and get latest record with first record from lat lng next one.

How I can write this query ?

I wrote this query, and I need to get first and last record base date now !

SELECT mDate as date ,xLat as lat,yLng as lng
FROM ReportAct_tbl ,
 (SELECT mDate as dt,xLat as lt,yLng as lg
 FROM ReportAct_tbl )
WHERE date = dt and lat = lt and lng = lg
order by date ASC,lat ASC,ylng ASC

Answer 1

As I understand it sqlite does not have window functions which really simplify these kind of queries (I replaced a couple of identifiers which I suspect is reserved words):

select dt, tm, lat, lng
from (
 select dt, tm, lat, lng
 , row_number() over (partition by dt, lat, lng 
 order by tm asc) rn1
 , row_number() over (partition by dt, lat, lng 
 order by tm desc) rn2
 from ReportAct_tbl
) x
where 1 in (x.rn1, x.rn2)

Without window function (like row_number) it's probably easiste to get the row where its does not exists any rows that date before that time

select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
 select 1 
 from ReportAct_tbl r2
 where r1.dt = r2.dt
 and r1.lat = r2.lat
 and r1.lng = r2.lng
 and r1.tm < r2.tm
)

To get the last rows we do the same:

select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
 select 1 
 from ReportAct_tbl r2
 where r1.dt = r2.dt
 and r1.lat = r2.lat
 and r1.lng = r2.lng
 and r1.tm > r2.tm
)

and finally we can combine them as:

select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
 select 1 
 from ReportAct_tbl r2
 where r1.dt = r2.dt
 and r1.lat = r2.lat
 and r1.lng = r2.lng
 and r1.tm < r2.tm
)
union
select dt, tm, lat, lng
from ReportAct_tbl r1
where not exists (
 select 1 
 from ReportAct_tbl r2
 where r1.dt = r2.dt
 and r1.lat = r2.lat
 and r1.lng = r2.lng
 and r1.tm > r2.tm
)