'group by' works on MySQL, but not Oracle

The reason you encounter the ORA error is because MySQL supports non-standard GROUP BY clauses, calling it a "feature". It's documented here.

The standard SQL GROUP BY clause must include ALL columns specified in the SELECT clause, that are not wrapped in aggregate functions (LIKE COUNT, MAX/MIN, etc), to be specified in the GROUP BY clause.

If you want one, unique row per http_session_id value - look at using ROW_NUMBER:

SELECT x.*  FROM (select http_session_id, unique_row_id, page_name, page_hit_timestamp,               ROW_NUMBER() OVER (PARTITION BY http_session_id                                       ORDER BY http_session_id) AS rank          FROM page_hits) x WHERE x.rank = 1

Would this work:

select max(unique_row_id), http_session_idfrom page_hitsgroup by http_session_id

Incidentally; what does my sql return in your resultset for columsn that are included in the resultset but not in the group by clause? (page_name, page_hit_timestamp)

I think that a GROUP BY requires a variable to have been used in a WHERE clause or aggregation function in the SQL standard?

Try using SELECT MAX(unique_row_id) GROUP BY http_session_id.