Help with SQL Query

[Scott_274]

I have a table with 75,000+ records which is used to define assets. As assets become upgraded a new entry with the same id gets inserted into the table with a timestamp so one id can have many records. I am trying to pull out the max(timestamp) for each id (which i can do) but I can't seem to do it for all of the fields in the table.

This works: select tag_id,max(to_char(time_stamp,'fmDD-MON-YYYY HH:MI:SS')) from temp_track2 group by tag_id


But when I add extra fields it changes the number of rows because I guess it is grouping them differently. I am trying to insert the output into a new table. Any help would be greatly appreciated.

Thanks

[Volker Borowski · Accepted solution]

Hi Scott,

since you use a group function (MAX), you cannot use additional fields.

Way out one (Expensive in terms of performance):
Create a VIEW with this select and equal-join the table against the view

CREATE VIEW MYRESULT AS SELECT
tag_id, max(...) as my_timestamp from ...

SELECT b.field1, b.field2, ... from MYRESULT a, temp_track2 b where
a.tag_id=b.tag_id AND
a.my_timestamp=to_char(b.time_stamp...);

Way out two (Expensive in terms of design):
Dump the Timestamp as a key-field and use a technical ID for the key. Your application could in this case always do

select from table where
tech_id in (...any Subselect delivering tech_ids...)

Hope this helps
Volker

[Brian Crabtree]

Scott,

You can also do this as a subquery as well:

select tag_id,to_char(time_stamp,'fmDD-MON-YYYY HH:MI:SS') from temp_track2 a where timestamp = (select max(timestamp) from temp_track2 b where a.tag_id = b.tag_id)

I think this will work, although it might have a syntax error. You should be able to add additional fields into the statement without needing the group by though.

Thanks,

Brian