Re: Select latest Timestamp values with group by

[Adarsh Sharma <adarsh.sharma@xxxxxxxxxx>]

Hi Craig :-

Below is the schema of my table :-

CREATE TABLE demo_table
(
 id character varying NOT NULL,
 lat double precision,
 lon double precision,
 speed double precision,
 dt_stamp timestamp without time zone DEFAULT now(),
 CONSTRAINT gps_tracker_pkey PRIMARY KEY (id)
)
WITH (
 OIDS=FALSE);

I let u know after some work on Window functions

Thanks
Craig Ringer wrote:
> 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