Re: Select latest Timestamp values with group by - Mailing list pgsql-general

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