Get only max values from table for each ID record

Question

I am having table like this, where id is repeating several times.

I need to get only max L1 value for each ID, so correct result from the query would be:

L1	ID	VALUE_VARCHAR
1	224754148	xDSL 1 - node_id atribut
2	224754147	adef_node_id_test_99
4	224754121	node_id_bijelnia_dvorovi
4	244378018	1

Table:

INSERT INTO MY_TABLE(L1, ID, VALUE_VARCHAR) VALUES (1, 224754121, 'node_id_bijelnia_dvorovi');

INSERT INTO MY_TABLE(L1, ID, VALUE_VARCHAR) VALUES (1, 224754147, 'adef_node_id_test_99');

INSERT INTO MY_TABLE(L1, ID, VALUE_VARCHAR) VALUES (1, 224754148, 'xDSL 1 - node_id atribut');

INSERT INTO MY_TABLE(L1, ID, VALUE_VARCHAR) VALUES (1, 244378018, '1');

INSERT INTO MY_TABLE(L1, ID, VALUE_VARCHAR) VALUES (2, 224754121, 'node_id_bijelnia_dvorovi');

INSERT INTO MY_TABLE(L1, ID, VALUE_VARCHAR) VALUES (2, 224754147, 'adef_node_id_test_99');

INSERT INTO MY_TABLE(L1, ID, VALUE_VARCHAR) VALUES (2, 244378018, '1');

INSERT INTO MY_TABLE(L1, ID, VALUE_VARCHAR) VALUES (3, 224754121, 'node_id_bijelnia_dvorovi');

INSERT INTO MY_TABLE(L1, ID, VALUE_VARCHAR) VALUES (3, 244378018, '1');

INSERT INTO MY_TABLE(L1, ID, VALUE_VARCHAR) VALUES (4, 224754121, 'node_id_bijelnia_dvorovi');

INSERT INTO MY_TABLE(L1, ID, VALUE_VARCHAR) VALUES (4, 244378018, '1');

If you only need to take the maximum value of L1 and don't need other field data, you only need a simple GROUP BY ID and then MAX(L1) to get it — Ian, May 13 '22 at 08:02

score 1 · Answer 1 · answered May 13 '22 at 07:31

Analytic functions, such as row_number, help:

SQL> with temp as
  2    (select l1, id, value_varchar,
  3            row_number() over (partition by id order by l1 desc) rn
  4     from my_table
  5    )
  6  select l1, id, value_varchar
  7  from temp
  8  where rn = 1
  9  order by id;

        L1         ID VALUE_VARCHAR
---------- ---------- ------------------------------
         4  224754121 node_id_bijelnia_dvorovi
         2  224754147 adef_node_id_test_99
         1  224754148 xDSL 1 - node_id atribut
         4  244378018 1

SQL>

score 1 · Answer 2 · answered May 13 '22 at 07:33

WITH sorted_data AS
(
  select l1, id, value_varchar, rank() OVER (PARTITION BY id ORDER BY  l1 DESC) as r from my_table
)
SELECT * FROM sorted_data WHERE r = 1;

4   224754121   node_id_bijelnia_dvorovi    1
2   224754147   adef_node_id_test_99    1
1   224754148   xDSL 1 - node_id atribut    1
4   244378018   1   1

Toni Antunović · Accepted Answer · 2022-05-13T09:17:14.503

Try using max(l1)over window function:

select distinct max(l1)over (partition by idn),idn,value_varchar
from my_table
order by max(l1)over (partition by idn)

Sample:

create table my_table (l1 number, idn number, value_varchar varchar2(100));

INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (1, 224754121, 'node_id_bijelnia_dvorovi');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (1, 224754147, 'adef_node_id_test_99');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (1, 224754148, 'xDSL 1 - node_id atribut');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (1, 244378018, '1'); 
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (2, 224754121, 'node_id_bijelnia_dvorovi');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (2, 224754147, 'adef_node_id_test_99');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (2, 244378018, '1');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (3, 224754121, 'node_id_bijelnia_dvorovi');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (3, 244378018, '1');
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (4, 224754121, 'node_id_bijelnia_dvorovi'); 
INSERT INTO MY_TABLE(L1, IDn, VALUE_VARCHAR) VALUES (4, 244378018, '1');

Result:

1   224754148   xDSL 1 - node_id atribut
2   224754147   adef_node_id_test_99
4   224754121   node_id_bijelnia_dvorovi
4   244378018   1

Get only max values from table for each ID record

3 Answers3