Select latest Timestamp values with group by

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

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

Any update on the below query :-
I tried the below query but :-
*
select bb_id,lat,lon,max(dt_stamp) from gps_tracker group by bb_id;
*
ERROR: column "gps_tracker.lat" must appear in the GROUP BY clause or
be used in an aggregate function
LINE 1: select bb_id,lat,lon,max(dt_stamp) from gps_tracker group by...
^

********** Error **********

ERROR: column "gps_tracker.lat" must appear in the GROUP BY clause or
be used in an aggregate function
SQL state: 42803
Character: 14

I think this can be solved by applying some function to lat lon values
of max(dt_stamp) row.

Thanks

Adarsh Sharma wrote:

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