Thread: Select latest Timestamp values with group by

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 ).

Snapshot of small dataset :-

"3903";"661000212";34.3000000312542;74.4666670842472;0;"2011-10-10
12:47:33.360572"
"3904";"661000212";24.816667028954;93.9500000453283;0;"2011-10-10
12:47:33.360572"
"3905";"661000212";24.816667028954;93.9500000453283;0;"2011-10-20
12:47:33.360572"
"3906";"661000213";24.816667028954;93.9500000453283;0;"2011-10-10
12:47:33.360572"
"3907";"661000212";24.816667028954;93.9500000453283;0;"2011-10-10
12:47:33.360572"
"3908";"661000212";24.816667028954;93.9500000453283;0;"2011-10-10
12:47:33.360572"
"3909";"661000212";25.1166670062712;94.3666669968243;0;"2011-10-10
12:47:33.360572"
"3910";"661000213";26.8491101532852;92.8058205131302;0;"2011-10-10
12:47:33.360572"
"3911";"661000212";26.8491101532852;92.8058205131302;0;"2011-10-14
12:47:33.360572"


Can anyone let me know the query for that.


Thanks

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

Thanks Craig, but I solved the issue by the below query :-

Here is the query for that :-

select bb_id,lat,lon,speed,dt_stamp from demo_table
inner join
  (select bb_id as did, max(dt_stamp) as ts
  from demo_table group by bb_id) as ds
  on demo_table1.bb_id = ds.did and demo_table1.dt_stamp = ds.ts;


Best Regards
Adarsh




Adarsh Sharma wrote:

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

On 11/10/11 12:55, Adarsh Sharma wrote:

Thanks Craig, but I solved the issue by the below query :-

Here is the query for that :-

select bb_id,lat,lon,speed,dt_stamp from demo_table
inner join
  (select bb_id as did, max(dt_stamp) as ts
  from demo_table group by bb_id) as ds
  on demo_table1.bb_id = ds.did and demo_table1.dt_stamp = ds.ts;

Yep, so you used the self-join method. That works, though it's probably quite a lot slower than using a window function for the job.

I'm glad you found a suitable solution for your needs.

--
Craig Ringer