Preserve order from subquery (with GROUP BY and ORDER BY)
<p>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.</p>

<p>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.</p>

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

<pre><code>+------------+------------+-----------+
| 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 |
+------------+------------+-----------|
</code></pre>

<p>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:</p>

<pre><code>+------------+------------+-------+
| latitude   | longitude  | count |
+------------+------------+-------+
| 43.1784771 | -8.5956853 | 2     |
| 41.2784813 | -7.5956853 | 1     |
| 42.1786173 | -8.5951757 | 2     |
+------------+------------+-------|
</code></pre>

<p>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:</p>

<pre class="lang-sql prettyprint-override"><code>SELECT latitude, longitude, count(*)
FROM 
    (SELECT latitude, longitude, timestamp FROM table ORDER BY timestamp asc) subquery1
GROUP BY latitude, longitude
</code></pre>

<p>I have been looking in StackOverflow for answers and the closest was this one: <a href="https://stackoverflow.com/questions/16248813/is-order-in-a-subquery-guaranteed-to-be-preserved">Is order in a subquery guaranteed to be preserved?</a>
However, it is not working in my case because I need the "group by" clause. Can anyone help me, please?</p>