Re: Select latest Timestamp values with group by

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