'group by' works on MySQL, but not Oracle

Question

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.

Answer 1

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

Answer 2

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)