I have this.

And I need to select to this. Make order for distincted item_id

CodePudding user response：

If you just want to order by the minimum ORDER for each item_id then:

SELECT item_id,
       ROW_NUMBER() OVER (ORDER BY MIN("ORDER")) AS "ORDER"
FROM   table_name
GROUP BY item_id

Which, for the sample data:

CREATE TABLE table_name (ID, ITEM_ID, "ORDER") AS
SELECT 10, 1, 1 FROM DUAL UNION ALL
SELECT 11, 1, 2 FROM DUAL UNION ALL
SELECT 12, 1, 3 FROM DUAL UNION ALL
SELECT 13, 4, 4 FROM DUAL UNION ALL
SELECT 15, 2, 5 FROM DUAL UNION ALL
SELECT 16, 2, 6 FROM DUAL UNION ALL
SELECT 17, 3, 7 FROM DUAL UNION ALL
SELECT 18, 3, 8 FROM DUAL;

Note: ORDER is a reserved word and cannot be used as an unquoted identifier; it would be better to use a different identifier rather than ORDER.

Outputs:

ITEM_ID	ORDER
1	1
4	2
2	3
3	4

If you want item_id to appear multiple times if there are different item_ids between occurrences then, from Oracle 12, you can use MATCH_RECOGNIZE:

SELECT *
FROM   table_name
MATCH_RECOGNIZE(
  ORDER BY "ORDER"
  MEASURES
    FIRST(item_id) AS item_id,
    MATCH_NUMBER() AS "ORDER"
  PATTERN (same_item )
  DEFINE same_item AS FIRST(item_id) = item_id
)

Which for the sample data above gives the same output; however, if you add some extra rows:

INSERT INTO table_name (id, item_id, "ORDER")
SELECT 19, 1,  9 FROM DUAL UNION ALL
SELECT 20, 2, 10 FROM DUAL UNION ALL
SELECT 21, 2, 11 FROM DUAL UNION ALL
SELECT 22, 4, 12 FROM DUAL;

Then the output from the MATCH_RECOGNIZE query is:

ITEM_ID	ORDER
1	1
4	2
2	3
3	4
1	5
2	6
4	7

db<>fiddle here