Get the max value from each group oracle

Question

I want to get the top value in each group based on ID_DATE column from the following record. Group by ID_TOPIC

CREATE TABLE DA_TBL( DATA_ID VARCHAR2(50), REF_DESC VARCHAR2(50), DATE_L DATE NOT NULL, ID_TOPIC VARCHAR2(50), ID_DATE NUMBER ); INSERT all INTO DA_TBL VALUES ('1','sample 1',CURRENT_TIMESTAMP, 'local', 1) INTO DA_TBL VALUES ('2','sample 2',CURRENT_TIMESTAMP, 'tradition', 2) INTO DA_TBL VALUES ('3','sample 2',CURRENT_TIMESTAMP, 'gospel', 3) INTO DA_TBL VALUES ('4','sample 4',CURRENT_TIMESTAMP, 'local', 4) INTO DA_TBL VALUES ('5','sample 5',CURRENT_TIMESTAMP, 'gospel', 5) INTO DA_TBL VALUES ('6','sample 6',CURRENT_TIMESTAMP, 'tradition', 6) INTO DA_TBL VALUES ('7','sample 7',CURRENT_TIMESTAMP, 'gospel', 7) INTO DA_TBL VALUES ('8','sample 8',CURRENT_TIMESTAMP, 'local', 8) INTO DA_TBL VALUES ('9','sample 9',CURRENT_TIMESTAMP, 'tradition', 9) INTO DA_TBL VALUES ('10','sample 10',CURRENT_TIMESTAMP, 'local', 10) INTO DA_TBL VALUES ('11','sample 11',CURRENT_TIMESTAMP, 'gospel', 11) SELECT * FROM dual;

What I want is:

DATA_ID|REF_DESC |ID_TOPIC |ROWNUMBER| -------|---------|---------|---------| 9 |sample 9 |tradition| 1| 10 |sample 10|local | 1| 11 |sample 11|gospel | 1|

What I get is:

DATA_ID|REF_DESC|ID_TOPIC |ROWNUMBER| -------|--------|---------|---------| 9 |sample 9|tradition| 1| 8 |sample 8|local | 1| 7 |sample 7|gospel | 1|

What I have tried

 SELECT * FROM (SELECT DATA_ID, REF_DESC, ID_TOPIC , ROW_NUMBER() OVER (PARTITION BY ID_TOPIC ORDER BY DATA_ID DESC) AS rownumber FROM DA_TBL ORDER BY DATA_ID DESC) WHERE rownumber = 1;

Littlefoot · Accepted Answer · 2020-03-03 10:38:57Z

Your problem is DATA_ID column's datatype. It is a string, and strings are sorted differently than numbers. 9 is "larger" than 10.

If you can, modify column's datatype to NUMBER.

Or, apply TO_NUMBER here:

ROW_NUMBER() OVER (PARTITION BY ID_TOPIC ORDER BY TO_NUMBER(DATA_ID) DESC) ------------------ here

This might not work if column contains alphanumeric values.

SQL> l 1 SELECT * 2 FROM (SELECT DATA_ID, REF_DESC, ID_TOPIC 3 , ROW_NUMBER() OVER (PARTITION BY ID_TOPIC ORDER BY to_number(DATA_ID) DESC) AS rownumber 4 FROM DA_TBL ORDER BY DATA_ID DESC) 5* WHERE rownumber = 1 SQL> / DATA_ID REF_DESC ID_TOPIC ROWNUMBER ------- ---------- ---------- ---------- 9 sample 9 tradition 1 11 sample 11 gospel 1 10 sample 10 local 1 SQL>

Gosfly · Accepted Answer · 2020-03-03 10:45:06Z

Try the following query :

SELECT t1.DATA_ID, t1.REF_DESC, t1.ID_TOPIC FROM yourTable t1 JOIN ( SELECT ID_TOPIC, MAX(ID_DATE) AS maxIdDate FROM yourTable GROUP BY ID_TOPIC ) t2 ON t1.ID_TOPIC = t2.ID_TOPIC AND t1.ID_DATE = t2.maxIdDate

I think you used the wrong column in your query, so it should also work if you fix it this way :

SELECT * FROM ( SELECT DATA_ID, REF_DESC, ID_TOPIC, ROW_NUMBER() OVER (PARTITION BY ID_TOPIC ORDER BY ID_DATE DESC) AS rownumber FROM DA_TBL ) t WHERE rownumber = 1;

Have you tried? I am get SQL Error [918] [42000]: ORA-00918: column ambiguously defined
I agree that data_id is not a number as it should but i am using ID_DATE here which is a number "I want to get the top value in each group based on ID_DATE"

Michał Turczyn · Accepted Answer · 2020-03-03 10:39:22Z

This is because you have varchar datatype on DATA_ID.

For correct ordering (you expect integer ordering, not a text order), you need to cast it:

ROW_NUMBER() OVER (PARTITION BY ID_TOPIC ORDER BY CAST(DATA_ID AS NUMBER) DESC)

Side note: datatypes in your tables are wrong - if you have an ID, it naturally should be stored as a number (unless you have some special requirements).

From what you posted, you store two ID columns as varchar, it should be number instead.

Then change it, if you can - it will really repay you in future. As you can see, this can cause problems...

Gordon Linoff · Accepted Answer · 2020-03-03 11:37:01Z

I want to get the top value in each group based on ID_DATE

If so, why are you focusing on DATA_ID?

The query you want is:

SELECT * FROM (SELECT DATA_ID, REF_DESC, ID_TOPIC , ROW_NUMBER() OVER (PARTITION BY ID_TOPIC ORDER BY ID_DATE DESC) AS rownumber FROM DA_TBL ) WHERE rownumber = 1 ORDER BY TO_NUMBER(DATA_ID) DESC;

Here is a db<>fiddle.

Collectives™ on Stack Overflow

Get the max value from each group oracle

4 Answers 4

Comments

4 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

4 Comments

1 Comment

Comments

Related