"group by" works on MySQL but not on Oracle

Question

I have a query that works on MySQL but does not 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 

SQL

 select http_session_id, unique_row_id, page_name, page_hit_timestamp from page_hits group by http_session_id; 

In MySQL, this will return 3 rows (one for each unique http_session_id).

In Oracle, I get the message "ORA-00979: not a GROUP BY clause". I also tried playing with clear ones, but I can't get it to work.

Just to be clear - I need a ResultSet containing one line per unique http_session_id. It is preferable that unique_row_id be maximum (e.g. 2 for http_session_id == 123456789), but that does not matter.

I am on the verge of breaking up into several separate sql statements (one "select a separate http_session_id" and the other iterate over all of these and select max (unique_row_id). Any pointers would be greatly appreciated - I would love to avoid this!

Rgds, Kevin.

Answer 1

Will this work:

 select max(unique_row_id), http_session_id from page_hits group by http_session_id 

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

Answer 2

The reason you encounter an ORA error is because MySQL supports non-standard GROUP BY clauses, calling it a "function". This is 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.) that must be specified in the GROUP BY clause.

If you need one unique line for the value of http_session_id - 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 3

I think GROUP BY requires a variable to be used in a WHERE clause or an aggregation function in the SQL standard?

Try using SELECT MAX(unique_row_id) GROUP BY http_session_id .

Answer 4

In standard SQL, if you have a GROUP BY clause, all columns that are not part of this should be in aggregates. In MySQL, this rule has been softened by design.

For example, this is allowed in MySQL, but not in standard SQL:

 SELECT customer_id, country, SUM(amount) FROM records GROUP BY customer_id 

There is one caveat: MySQL assumes that you know what you are doing. If the same client has entries in several countries, the request will simply capture the first country in the table, not paying attention to all the others. In addition, since the order of the strings is undefined, and there is no ORDER BY, you can get different results each time the query is run.

In standard SQL, you have two options:

 SELECT customer_id, country, SUM(amount) FROM records GROUP BY customer_id, country 

or

 SELECT customer_id, MIN(country), SUM(amount) FROM records GROUP BY customer_id 

Answer 5

Another option in Oracle if you want:

 select DISTINCT FIRST_VALUE(unique_row_id) OVER (PARTITION BY http_session_id ORDER BY unique_row_id DESC) unique_row_id, http_session_id, FIRST_VALUE(page_name) OVER (PARTITION BY http_session_id ORDER BY unique_row_id DESC) page_name, FIRST_VALUE(page_hit_timestamp) OVER (PARTITION BY http_session_id ORDER BY unique_row_id DESC) page_hit_timestamp from page_hits; 

This will get a separate set of http_session_id 's, and for each returns unique_row_id , page_name and page_hit_timestamp from the row with the largest unique_row_id for this http_session_id , for example:

 unique_row_id http_session_id page_name page_hit_timestamp ---------------------------------------------------------------- 2 123456789 faq.html 2010-01-20 15:00:15 5 987654321 info.html 2010-01-20 16:00:15 8 111111111 info.html 2010-01-20 16:01:15