Oracle – How to Sort Categorical Values by Arbitrary Order

Oracle solution to both problems:

SELECT ID, Category FROM (
   SELECT FIRST_VALUE(ID) OVER 
      (PARTITION BY Category ORDER BY Ord) IDOfSmallestOrdForCategory
      , Category, ID FROM DATAS
   )
   WHERE ID = IDOfSmallestOrdForCategory;

Generic solution to both problems:

SELECT MIN(a.ID), a.Category FROM DATAS a
JOIN (SELECT CATEGORY, COALESCE(MIN(ORD),0) MINORD FROM DATAS GROUP BY CATEGORY) b
ON COALESCE(a.ORD,0) = b.MINORD AND a.Category = b.Category
GROUP BY a.Category;

Oracle DDL/DML:

CREATE TABLE DATAS (
    ID       Integer,
    CATEGORY Integer,
    ORD      Integer 
);

INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 2);

INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3); 
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2); 
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1); 
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 1); 
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (6, 3, NULL);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (7, 3, NULL);

The question is which of the pdates for a module do you want to use for ordering purposes?

If you are happy to use the largest, you could use:

select module from logging order by max(pdate); 

--- EDIT

I think I get it now - you probably want something like this:

select module
from( select module, 
             sum(module_step) over (order by pdate rows unbounded preceding) 
                 as batch_number, 
             pdate
      from( select module, 
                   case lag(module) over (order by pdate) when module then 0 
                        else 1 end as module_step, 
                   pdate from logging) )
group by module, batch_number
order by batch_number;