Order by column then by first result

oracle, plsql, sql, sql-order-by

Solution

You can use analytics:

SQL> WITH data AS (
  2     SELECT 8 id, to_date('01/06/10') dat, 222 req FROM DUAL
  3     UNION ALL SELECT 4, to_date('01/08/10'), 222  FROM DUAL
  4     UNION ALL SELECT 7, to_date('04/07/10'), 100  FROM DUAL
  5     UNION ALL SELECT 2, to_date('09/09/10'), 100  FROM DUAL
  6     UNION ALL SELECT 5, to_date('08/07/10'), 100  FROM DUAL
  7     UNION ALL SELECT 6, to_date('06/07/10'), 111  FROM DUAL
  8     UNION ALL SELECT 1, to_date('10/09/10'), 111  FROM DUAL
  9     UNION ALL SELECT 3, to_date('13/09/10'), 111  FROM DUAL
 10  )
 11  SELECT ID, dat, req
 12    FROM DATA
 13   ORDER BY MIN(dat) over (PARTITION BY req), req, dat;

        ID DAT                REQ
---------- ----------- ----------
         8 01/06/2010         222
         4 01/08/2010         222
         7 04/07/2010         100
         5 08/07/2010         100
         2 09/09/2010         100
         6 06/07/2010         111
         1 10/09/2010         111
         3 13/09/2010         111

Problem

I'm trying to create a query in pl/sql to get the following result: Data: ``` Id | Date | Request 1 | 10/09/10 | 111 2 | 09/09/10 | 100 3 | 13/09/10 | 111 4 | 01/08/10 | 222 5 | 08/07/10 | 100 6 | 06/07/10 | 111 7 | 04/07/10 | 100 8 | 01/06/10 | 222 ``` To get the folowing result: ``` Id | Date | Request 8 | 01/06/10 | 222 4 | 01/08/10 | 222 7 | 04/07/10 | 100 2 | 09/09/10 | 100 5 | 08/07/10 | 100 6 | 06/07/10 | 111 1 | 10/09/10 | 111 3 | 13/09/10 | 111 ``` Ps: in this exaxmple the format date is (dd/mm/yy) As you can see on the example, firstly order by Date, but when catch the first row, list all the rows with the same Request, when finish the rows with the same Request, keep ordering by Date... Can anyone help me with this query? I have tried a few ways, but with no expected result.

Original source