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.

to use MIN (it is an aggregate function), you have to use the GROUP BY clause to, to group your records. — Esteban P., Aug 10 '17 at 15:32
You should show some sample data & desired output. As it is, as Glenn says, just ordering by c.LABEL _seems_ like it's what you need. — Mat, Aug 10 '17 at 15:35
I cannot use just order by as doing that gives me duplicate results because of join. — viv kumar, Aug 10 '17 at 15:38
Try This: https://stackoverflow.com/questions/19761714/using-the-min-function-in-the-having-clause — DreadedFrost, Aug 10 '17 at 15:40
Without a GROUP BY clause you have only one row group (all rows in your join) and one value of min(c.LABEL): sorting by a constant key doesn't make sense. — Lorenzo Gatti, Aug 10 '17 at 15:42
You don't select any column from c. At least c.ID and c.LABEL are important and selecting them would make the results more comprehensible. — Lorenzo Gatti, Aug 10 '17 at 15:47
This looks like an [XY problem](http://xyproblem.info). Please explain in words what data you have and what you want to get out of it. — William Robertson, Aug 11 '17 at 07:16

score 1 · Accepted Answer · answered Aug 10 '17 at 15:37

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;

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

1 Answers1