I want to do something like this:
select MAX(field1), field2 from tbl1 group by field1
But the above query as is doesn't work (sqlplus throws an error). How can I achieve above in a single query (for now, I have split it into two queries to get the result).
You could use an inline view and an analytic function (
max() over()) in it for selecting the row(s) with the largest timestamp:
select field1, field2 from (select field1, field2, max(field1) over() as max_field1 from tbl1) where field1 = max_field1;
Note that if there are many records with a timestamp value of
max_field1, they are all returned (in arbitrary order).