'Get only max values from table for each ID record [duplicate]

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

enter image description here

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');
sqloraclegreatest-n-per-group


Solution 1:[1]

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

Solution 2:[2]

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>

Solution 3:[3]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Littlefoot
Solution 3 Koen Lostrie

Related Questions