0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.