Preserve order from subquery (with GROUP BY and ORDER BY)

order by in subquery oracle
sql-order by subquery column
order by subquery postgres
oracle subquery order by missing right parenthesis
order by is not allowed in subqueries teradata

I am using a smartphone to collect data from the accelerometer and then saving it in a postgresql database, in a server. Basically, each time I read the accelerometer, I save the latitude/longitude at which the smartphone is at the moment, as well as the timestamp where it happened.

Now, I want to read from the database every distinct position (latitude/longitude) in the same order as they were saved (ordered by the timestamp). And I want to know how many readings are repeated in each position.

Let me explain with an example. Consider I have the following table in my database:

+------------+------------+-----------+
| latitude   | longitude  | timestamp |
+------------+------------+-----------+
| 43.1784771 | -8.5956853 | 930560045 |
| 43.1784771 | -8.5956853 | 930560054 |
| 41.2784813 | -7.5956853 | 930560063 |
| 42.1786173 | -8.5951757 | 930560072 |
| 42.1786173 | -8.5951757 | 930560082 |
+------------+------------+-----------|

Notice that I have the elements ordered by timestamp, and that I have 2 repeated positions. So, I want to query the database to see the repeated positions and have the following result:

+------------+------------+-------+
| latitude   | longitude  | count |
+------------+------------+-------+
| 43.1784771 | -8.5956853 | 2     |
| 41.2784813 | -7.5956853 | 1     |
| 42.1786173 | -8.5951757 | 2     |
+------------+------------+-------|

The problem is that I want the elements ordered as the original table (ordered by timestamp). I am trying the following query but it is not working, because the order in a subquery doesn't matter:

SELECT latitude, longitude, count(*)
FROM 
    (SELECT latitude, longitude, timestamp FROM table ORDER BY timestamp asc) subquery1
GROUP BY latitude, longitude

I have been looking in StackOverflow for answers and the closest was this one: Is order in a subquery guaranteed to be preserved? However, it is not working in my case because I need the "group by" clause. Can anyone help me, please?

SELECT 
latitude, 
longitude, 
count(1) as "Count", 
min(timestamp) as "Start",
max(timestamp) as "End"

FROM table 
GROUP BY latitude, longitude
ORDER BY min(timestamp) asc

sql, SELECT latitude, longitude, count(1) as "Count", min(timestamp) as "Start", max(​timestamp) as "End" FROM table GROUP BY latitude,  I am wondering in particular about PostgreSQL. Given the following contrived example: SELECT name FROM (SELECT name FROM people WHERE age >= 18 ORDER BY age DESC) p LIMIT 10 Are the names re

create or replace function foo(
  out latitude numeric, 
  out longitude numeric,
  out cnt int,
  out start_time numeric,
  out end_time numeric
) returns setof record as $$
declare
  c record;
  p record;
  i int := 1;
begin
  select null into p;
  for c in (select * from table order by timestamp) 
  loop
    if p is null then
      start_time := c.timestamp;
    elsif p.latitude <> c.latitude and p.longitude <> c.longitude then
      latitude := p.latitude; 
      longitude := p.longitude;
      cnt := i;
      end_time := p.timestamp;
      return next;
      i := 1;
      start_time := p.timestamp;
    else
      i := i + 1;
    end if;
    p := c;
  end loop;
  if p.latitude = c.latitude and p.longitude = c.longitude then
    latitude := p.latitude; 
    longitude := p.longitude;
    cnt := i;
    end_time := p.timestamp;
    return next;
  end if;
  return;
end; $$ immutable language plpgsql;

Usage:

select * from foo();

As a little bonus it is also providing start/end timestamps for each series.

Why is ORDER BY in a FROM Subquery Ignored?, I am not sure if this is expected behaviour or not, but using GROUP BY in the subquery seems to force the overall query to accept the ORDER BY clause in the​  You should use it if the subquery uses some kind of LIMIT / TOP.. SQL Server will not allow it unless the subquery contains TOP or FOR XML clause as well:-- Fails WITH q(id) AS ( SELECT 1 UNION ALL SELECT 2 ) SELECT * FROM ( SELECT * FROM q ORDER BY id DESC ) q2 -- Succeeds WITH q(id) AS ( SELECT 1 UNION ALL SELECT 2 ) SELECT * FROM ( SELECT TOP 1 * FROM q ORDER BY id DESC ) q2 -- Succeeds

Ordering is not preserved for subqueries but it can be defined for array_agg operations and we can use that to determine a wider ordering. Try this for example:

SELECT latitude, longitude, count(*), (array_agg(timestamp order by timestamp))[1] as first_time
FROM table GROUP BY latitude, longitude;

In the OP's case, min(timestamp) might be simpler but if there is a more complex ordering this might be a neater option.

Is a "Ranking" query's ORDER BY in FROM subquery is preserved , ORDER BY is only ordering the results when: it is in the outermost (last) query; if GROUP BY is used (in the subquery); if a LIMIT  the order by in sub-query is a must to use if you need to delete some records based on some sorting. like . delete from someTable Where ID in (select top(1) from sometable where condition order by insertionstamp desc) so that you can delete the last insertion form table. there are three way to do this deletion actually. however, the order by in the sub-query can be used in many cases.

Removing redundant ORDER BY, Some SQL constructs such as ORDER BY do not affect query results in many As in the previous example preserving the ORDER BY just causes the So, unless a subquery contains one of these two clauses, the query  Rows in a table (or in a subquery in the FROM clause) do not come in any specific order. That's why the optimizer can ignore the ORDER BY clause that you have specified. In fact, the SQL standard does not even allow the ORDER BY clause to appear in this subquery (we allow it, because ORDER BY

Documentation: 9.5: SELECT, If the GROUP BY clause is specified, or if there are aggregate function calls, the If the ORDER BY clause is specified, the returned rows are sorted in the specified order. Each subquery can be a SELECT, TABLE, VALUES, INSERT, UPDATE or Keep in mind that all aggregate functions are evaluated before evaluating  When you use an ORDER BY clause in a view, an inline function, a derived table, or a subquery, it does not guarantee ordered output. Instead, the ORDER BY clause is only used to guarantee that the result set that is generated by the Top operator has a consistent makeup.

MySQL 8.0 Reference Manual :: 8.2.1.16 ORDER BY , The query has different ORDER BY and GROUP BY expressions. temporary files, the ORDER BY operation for queries (and subqueries) of the following form: You can apply the window function row_number() to remember the order of elements. However, with the usual row_number() OVER (ORDER BY col) you get numbers according to the sort order, not the original position in the string. You could simply omit ORDER BY to get the position "as is":

Comments
  • SELECT latitude, longitude, count(*) from table GROUP BY latitude, longitude ORDER BY max(timestamp); /* or min(timestamp)*/
  • The problem here is that you could be in the same location at two different time intervals. I, for example, go to work every day, and place my phone in the same exact global location from 9am to 5pm every day. This query, as you've described, does not show you that.
  • Hint: change [...] to "...". It is PostgreSQL, not MS SQL server.
  • Actually, a very simples solution! Thanks! I am aware of the problem of same location at two different time intervals but it is not an issue in my specific problem by now.
  • @MaxSorin, the below answer by Abelisto solves the problem of same location at different time intervals :)
  • Thanks! Your solution has the advantage of separate same location at two distinct time intervals. In my specific case I don't need that, but it is indeed something good to point out!
  • @tjiagoM Feel free to use it if it will ever needed :)