How to do order by min() of some column?

Question

I am pretty new to SQL. I want a query which should do order by on min of some column. Below is the query i want.

SELECT *
FROM   ( 
         SELECT p.PROJECT_ID,
                p.PROJECT_NAME,
                p.PROJECT_TYPE
         FROM   PROJECT p
                LEFT OUTER JOIN code c
                ON p.PROJECT_ID= c.PROJECT_ID
         WHERE  p.PROJECT_NAME IN ('test')
         ORDER BY min(c.LABEL) ASC
       )
WHERE  rownum <= 25;

Why i need it this way is. I have one table PROJECT.

PROJECT_ID  PROJECT_NAME    PROJECT_TYPE
1           a               test1
2           b               test2

i have another table code which has project_id as foreign key.

ID  PROJECT_ID  LABEL
1      1         a
2      1         b
3      1         c
4      2         d

now when i will join it on project_id and make order by on code.label it will give me 4 records three with project id 1 and 1 with project id 2. But my requirement is to sort the project based on the codes label. so logically i want two records . One for project id 1 with min vale of label of all the possible combinations of project id 1 i.e with label a and other with project id 2. So that's why i want to sort it based on min of code label. I cannot use group by as it will degrade the performance.

Answer 1

For use a MIN( ) you need a group by eg:

SELECT  *
    FROM  ( 
      SELECT p.PROJECT_ID,
              p.PROJECT_NAME,
              p.PROJECT_TYPE
      FROM PROJECT p
      LEFT OUTER JOIN code c
      ON p.codeId=c.ID
      WHERE p.PROJECT_NAME IN ('test')
      GROUP BY .PROJECT_ID,
              p.PROJECT_NAME,
              p.PROJECT_TYPE
      ORDER BY min(c.LABEL) ASC
      )
     WHERE rownum <= 25;

and in some db you must select the column you need for order by eg:

SELECT  *
    FROM  ( 
      SELECT p.PROJECT_ID,
              p.PROJECT_NAME,
              p.PROJECT_TYPE,
               min(c.LABEL)
      FROM PROJECT p
      LEFT OUTER JOIN code c
      ON p.codeId=c.ID
      WHERE p.PROJECT_NAME IN ('test')
      GROUP BY .PROJECT_ID,
              p.PROJECT_NAME,
              p.PROJECT_TYPE
      ORDER BY min(c.LABEL) ASC
      )
     WHERE rownum <= 25;