Once again, you are ignoring the fact that Oracle does not guarantee order of a result set without an explicit ORDER BY. In your example you are using an ORDER BY ROWNUM, but the ROWNUM pseudocolumn is applied after any rows are retrieved and ORDER BY ordering has taken place.
Your example is relying on the fact that Oracle will always retrieve the data in a specific order, so the ROWNUM will always be correct. That is simply not the case. In your simple example it appears to be, because all the data is stored in a single block, so Oracle *appears* to maintain the insert order. This is a fluke, not a rule.
Let me show you. I'll create a table with the same data you have, but I will insert the data in a different order.
- Code: Select all
CREATE TABLE my_tab (
INSERT INTO my_tab VALUES (102, 'Roberts');
INSERT INTO my_tab VALUES (102, 'Julie');
INSERT INTO my_tab VALUES (102, 'Mrs.');
INSERT INTO my_tab VALUES (101, 'Adams');
INSERT INTO my_tab VALUES (101, 'Neil');
INSERT INTO my_tab VALUES (101, 'Mr.');
Now let's use your solution.
- Code: Select all
COLUMN full_name FORMAT A40
MAX(SYS_CONNECT_BY_PATH(NAME, ',')) FULL_NAME
SELECT ID, NAME,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ROWNUM ) RN
CONNECT BY PRIOR ID=ID AND PRIOR RN = RN-1
START WITH RN=1
GROUP BY ID
ORDER BY ID;
2 rows selected.
Unless you have a specific way to order the data, which is *guaranteed* to always return the data in the same order, doing any processing that relies on the data being ordered is impossible.
Replying on insert order is flawed. It may appear to work for small tests, but Oracle can access blocks in any order it wants. It can also migrate rows to different blocks. This is why the Oracle manuals always state:
"Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order."http://docs.oracle.com/cd/E16655_01/ser ... SQLRF20039
So we go back to your original question. Either it is a trick question, or the person asking the question doesn't understand the flaw in the question.