1
select osmid,ST_X(shape),ST_Y(shape) 
from osmpoints 
where (osmpoints.osmid, osmpoints.osmtimestamp) 
     IN  (select osmid,MAX(osmtimestamp) 
          from osmPoints 
          GROUP BY osmid 
          Having MAX(osmtimestamp) <= '2019-09-16T01:23:55Z' 
            AND osmid in ('4426786454','1861591896','1861591869','1861591895',
                          '4426786455','2038185115','1861591853','6797739995',
                          '2299605892','6797739994','1861591898','2038185111','4426786454'));

when I run this query, I get sorted rows based on osmid column. but my question is how can I get rows in order of osmid that use in the IN clause?

Logica
  • 977
  • 4
  • 16
masoud
  • 11
  • 2
  • 2
    You don't get any sort order as you are not using `order by` - any order you see is pure coincidence. –  Jan 29 '20 at 10:01
  • 1
    Add an `ORDER BY` clause. Query result have no order, unless one is explicitly specified. Even storage order doesn't guarantee result order. The results seem sorted only by accident. Without an explicit ORDER BY, the server is free to return results in the cheapest way possible. Parallel processing or specific types of joins or ... aggregations can change the result order – Panagiotis Kanavos Jan 29 '20 at 10:02
  • https://stackoverflow.com/questions/866465/ –  Jan 29 '20 at 10:05
  • Not sure about postgree but mysql have field function to sort. Refer below link. https://stackoverflow.com/questions/396748/ordering-by-the-order-of-values-in-a-sql-in-clause – Pankaj_Dwivedi Jan 29 '20 at 10:09
  • @Pankaj_Dwivedi that's not what `FIELD()` does. `FIELD()` returns the index of a value in an array of values. `ORDER BY` sorts by that index then. The big problem with that is that the function must be applied to *every* row before the index and thus sort order can be calculated. That's very expensive – Panagiotis Kanavos Jan 29 '20 at 10:12
  • Unrelated, but: I don't think you need the sub-select and IN to begin with –  Jan 29 '20 at 10:42

1 Answers1

5

You can use the same technique as shown here:

with input(idlist) as (
  values (
    array['4426786454','1861591896','1861591869','1861591895',
         '4426786455','2038185115','1861591853','6797739995',
         '2299605892','6797739994','1861591898','2038185111','4426786454']::text[])
)
select p.osmid,ST_X(shape),ST_Y(shape) 
from osmpoints p
 cross join input
where (p.osmid, p.osmtimestamp) IN  (select osmid,MAX(osmtimestamp) 
                                     from osmPoints
                                        cross join input
                                     GROUP BY osmid 
                                     Having MAX(osmtimestamp) <= '2019-09-16T01:23:55Z' 
                                        AND osmid = any(idlist))
order by array_position(idlist, osmid);