On 10/10/2011 08:32 PM, Adarsh Sharma wrote:
Dear all,
I need to write a query to select latest rows with timestamp values.
My ID is repeated with lat lon and timestamp. I want the latest row of
each ID ( group by id ).
[snip]
"3911";"661000212";26.8491101532852;92.8058205131302;0;"2011-10-14
12:47:33.360572"
Can anyone let me know the query for that.
No, they can't. You only posted semicolon-separated data, not a schema
with column names or anything much else.
For a task like this you can use a window function, or you can self-join
and use a WHERE clause to match the greatest row. Using a window
function will be MUCH more efficient, so only use the self-join if
you're running on a really old version of PostgreSQL.
http://www.postgresql.org/docs/9.0/static/tutorial-window.html
http://www.postgresql.org/docs/9.0/static/functions-window.html
Using the first_value or last_value window functions with an ordering
clause to select the greatest timestamp within each window frame.
http://www.postgresql.org/docs/9.0/static/functions-window.html#FUNCTIONS-WINDOW-TABLE
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
