Query working in mySql and not working in Oracle

Question

Below is the query which is working in MySQL and not working in Oracle. Here I need to get the latest Date and corresponding event from table r for every ID in Table a . ID is unique.

 SELECT a.name , a.spids , a.group , a.id , r.date,r.event  FROM a
    LEFT OUTER JOIN ( SELECT id, MAX(date) AS latest FROM r  GROUP BY id ) AS rev
        ON rev.id = a.id
    LEFT OUTER JOIN r  ON r.id = rev.id
       AND r.date = rev.latest
     group by a.id order by ID;
    --------------------------------------------- 
    *The error in Oracle is "Not a group by function"
    I read several blogs about group by function and every where they are saying to use an aggregate funciton like sum , min,max. I tried but not able to get the results*
    -----------------------
    Table a ( It has other column - group also)
    -----------------------


   ID  Name   Spids  
    1   SIML    TRDR,THYU
    2   SIML    YUIL
    3   ghhe    yhgu,hjuy,kiuj
    4   th      yuio


   ----------------------- 
    Table r  ( Needs to get the latest updated date and corresponding event details for every ID)
    ----------------------- 


   ID     Event         Date
    1      by chris      02-02-2016
    1      by Steve      02-02-2013
    1      by gen        02-02-2014
    2      by Pete       12-12-2018
    2      by ken        01-02-2014
    3      by Mike       20-08-2018
    3      by chin       20-08-2017
    4      by chn        04-06-2012
    4      by tink       06-06-2017

Output should be like this ---------------------------

NAMe     SPIDS           GROUP      ID      DATE                  EVENT
    SIML    TRDR,THYU        Test        1    02-02-2016             by chris
    SIML    YUIL             Test        2    12-12-2018             by Pete 
    ghhe    yhgu,hjuy,kiuj   Test2       3    20-08-2018             by Mike
    th      yuio             Test1       4    06-06-2017             by tink

You typically GROUP BY the columns you select, except those who are arguments to set functions. — jarlh
– jarlh, Commented Aug 24, 2018 at 10:32

Gordon Linoff · Accepted Answer · 2018-08-24 10:33:49Z

1

Your select is:

select a.name , a.spids , a.group , a.id , r.date, r.event

Your group by is:

 group by a.id

These are incompatible -- what are the values of all the other columns in the select apart from a.id. That MySQL supports this is a (mis)feature of the database, not supported by almost any other database.

The most typical solution is to fix these so they are compatible:

select a.name, a.spids, a.group, a.id, max(r.date), max(r.event)

Your group by is:

 group by a.name , a.spids , a.group , a.id

In your case, the group by is probably not needed at all:

SELECT a.name, a.spids, a.group, a.id, r.date, r.event  
FROM a LEFT OUTER JOIN
     (SELECT id, MAX(date) AS latest
      FROM r
      GROUP BY id
     ) rev
     ON rev.id = a.id LEFT JOIN
     r 
     ON r.id = rev.id  AND r.date = rev.latest
ORDER BY ID;

You only need it if there are multiple rows with the same max date.

The most common solution in Oracle fixes this problem:

SELECT a.name, a.spids, a.group, a.id, r.date, r.event  
FROM a LEFT JOIN
     (SELECT r.*,
             ROW_NUMBER() OVER (PARTITION BY r.id ORDER BY r.date DESC) as seqnum
      FROM r
     ) rev
     ON rev.id = a.id AND seqnum = 1
ORDER BY ID;

answered Aug 24, 2018 at 10:33

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chaatat Over a year ago

Thankyou. I tried with "select a.name, a.spids, a.group, a.id, max(r.date), max(r.event) and group by as: group by a.name , a.spids , a.group , a.id " and it worked fine

Lajos Arpad · Accepted Answer · 2018-08-24 11:47:04Z

0

When you are doing a group by, you are aggregating the results, so the select should consider only aggregated columns, which is a.id and calls to aggregate functions in this case. Since a.id is a primary key in this specific example, your other columns of a can be used in the select clause in MySQL, quite logically, if you ask me. However, a column of r in such a group by is dangerous in terms of potential to err in MySQL as well, unless there is a single r the most for any record of a. You can solve this by listing all the non-aggregate columns used in the select clause into the group by clause.

answered Aug 24, 2018 at 11:47

Lajos Arpad

80.5k42 gold badges124 silver badges235 bronze badges

Collectives™ on Stack Overflow

Query working in mySql and not working in Oracle

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related