Sqlite – Query on table

sqlitesubquery

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

Best Answer

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
)
Related Question