CODEWITHSUNDEEP
sqlpostgresqlwindow-functionsgaps-and-islands
CodingWithRoyal
CodingWithRoyal

Reputation: 1123

PostgreSQL select rows having same column values

  | location_id  |  lat  |  long  |  speed  |
    ------------- ------- -------- --------- 
      101241        0.12    1.1       0.0    
    ------------- ------- -------- --------- 
      101242        0.12    1.1       0.0
    ------------- ------- -------- --------- 
      101243        0.12    1.1       0.0
    ------------- ------- -------- --------- 
      101244        1.25    0.74      7.4
    ------------- ------- -------- ---------

I want to select all locations where speed = 0 and lat && long are same

So from above example answer should be::

   | location_id  |
    --------------
        101241     
    --------------
        101242     
    --------------
        101243     
    --------------

Note:: Speed is constant 0 but lat and long depend on previous rows value

Upvotes: 2

Views: 2933

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

If you want adjacent rows, you can just use lead() and lag() . . . but with a little trick by using locationid:

select t.*
from (select t.*,
             lag(locationid) over (order by locationid) as prev_locationid,
             lead(locationid) over (order by locationid) as next_locationid,
             lag(locationid) over (partition by lat, long order by locationid) as prev_locationid_ll,
             lead(locationid) over (partition by lat, long order by locationid) as next_locationid_ll
      from t
     ) t
where speed = 0 and
      (prev_locationid = prev_locationid_ll or
       next_locationid = next_locationid_ll
      );

The comparison is of the location ids only. One is calculated based on the location id along. The second is the previous or next based on the latitude and longitude. When these are the same then the values are the same on adjacent rows.

Upvotes: 0

Slava Rozhnev
Slava Rozhnev

Reputation: 10163

One more solution:

SELECT location_id 
FROM device_location 
WHERE (lat, long) IN (
  SELECT lat, long
  FROM device_location
  WHERE speed = 0.0
  GROUP BY lat, long
  HAVING COUNT(*) > 1
);

Test it on SQLize.online

Upvotes: 0

GMB
GMB

Reputation: 222462

I actually read this as a gaps-and-islands problem, where you want adjacent rows that have the same latitude and longitude, and a speed of 0.

You could approach this with window functions: the difference between row numbers gives you the islands: you can then compute the lenght of each islands, and filter on those lenght is greater than 1 and whose speed is 0:

select *
from (
    select t.*, count(*) over(partition by lat, long, speed, rn1 - rn2) cnt
    from (
        select t.*, 
            row_number() over(order by location_id) rn1,
            row_number() over(partition by lat, long, speed order by location_id) rn2
        from mytable t
    ) t
) t
where speed = 0 and cnt > 1

Demo on DB Fiddle

Upvotes: 2

Andronicus
Andronicus

Reputation: 26046

You can use inner join:

select distinct t1.id
from table_name t1
inner join table_name t2
on t1.location_id <> t2.location_id 
and t1.lat = t2.lat
and t1.long = t2.long
where t1.speed = 0
and t2.speed = 0

or exists:

select t.id
from table_name t
where exists (
    select *
    from table_name it
    where t.location_id <> it.location_id 
    and t.lat = it.lat
    and t.long = it.long
    and it.speed = 0
)
and t.speed = 0

Upvotes: 2

Related Questions