Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
cancel
Showing results for 
Search instead for 
Did you mean: 

Help with SQL Query

SOLVED
Go to solution
Scott_274
Occasional Visitor

Help with SQL Query

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
2 REPLIES
Volker Borowski
Honored Contributor
Solution

Re: Help with SQL Query

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

Re: Help with SQL Query

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