I have a query that works on MySQL but doesn't work on Oracle, and I'm trying to convert. This is my table:
unique_row_id http_session_id page_name page_hit_timestamp
----------------------------------------------------------------
0 123456789 index.html 2010-01-20 15:00:00
1 123456789 info.html 2010-01-20 15:00:05
2 123456789 faq.html 2010-01-20 15:00:15
3 987654321 index.html 2010-01-20 16:00:00
4 987654321 faq.html 2010-01-20 16:00:05
5 987654321 info.html 2010-01-20 16:00:15
6 111111111 index.html 2010-01-20 16:01:00
7 111111111 faq.html 2010-01-20 16:01:05
8 111111111 info.html 2010-01-20 16:01:15
The SQL is
select http_session_id, unique_row_id, page_name, page_hit_timestamp
from page_hits
group by http_session_id;
On MySQL, this will return 3 rows (one for each unique http_session_id).
On Oracle, I get a "ORA-00979: not a GROUP BY expression" error. I've tried playing around with distinct too, but I can't get it to work.
Just to be clear - I would like a ResultSet that contains one row per unique http_session_id. It is preferable that the unique_row_id would be the max one (e.g. 2 for http_session_id==123456789), but this is not significant.
I'm on the verge of breaking this into multiple separate sql statements (one "select distinct http_session_id", and the other to iterate through all these and select the max(unique_row_id). Any pointers would be gratefully received - I would love to avoid this!
Rgds, Kevin.
Would this work:
select max(unique_row_id), http_session_id
from page_hits
group 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)