Search Postgresql Archives

Re: Select latest Timestamp values with group by

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux