12 Replies Latest reply: Oct 31, 2008 7:02 AM by 643647 RSS

    DISTINCT and ORDER BY

    401855
      SELECT DISTINCT fname, lname
      FROM employee
      ORDER BY job_id;

      For this to work job_id has to be part of the SELECT expression, but i'd like to know why since i readily can't see any reason.

      Regards
      Fergus
        • 1. Re: DISTINCT and ORDER BY
          Paul M.
          You should get the ORA-01791 error, and about it Oracle documentation says :

          ORA-01791 not a SELECTed expression

          Cause: There is an incorrect ORDER BY item. The query is a SELECT DISTINCT query with an ORDER BY clause. In this context, all ORDER BY items must be constants, SELECT list expressions, or expressions whose operands are constants or SELECT list expressions.

          Action: Remove the inappropriate ORDER BY item from the SELECT list and retry the statement.
          • 2. Re: DISTINCT and ORDER BY
            BrianCamire
            How would you expect the results to be ordered if for a distinct combination of fname and lname there were more than one values of job_id?
            • 3. Re: DISTINCT and ORDER BY
              422895
              This error is logically correct. Consider this example:
              FNAME                LNAME                    JOB_ID
              -------------------- -------------------- ----------
              Tom                  Lee                          10
              Tom                  Lee                           5
              Ann                  Jackstat                      6
              If you perform above select there should be only 2 records displayed. But the order of records is unpredictable. Since Tom Lee has two job_id, one of them is less than Ann's id ( 5<6 ), the other one is greater ( 10>6 ) - so should this record be displayed first of second one?
              • 4. Re: DISTINCT and ORDER BY
                401855
                Vyacheslav,
                Thank you so much as i certainly didn't anticipate such a scenario. It never occurred to me that thats why Oracle don't allow such combinations in the first place. This is one that has bugged me for a long time.

                Fergus :)
                • 5. Re: DISTINCT and ORDER BY
                  Laurent Schneider
                  SELECT fname, lname
                  from
                  ( select DISTINCT fname, lname, job_id
                  FROM employee
                  ) ORDER BY job_id;
                  • 6. Re: DISTINCT and ORDER BY
                    401855
                    Nice!

                    Fergus
                    • 7. Re: DISTINCT and ORDER BY
                      609723
                      hi,
                      the given query still returns three rows. But only two are needed. how that can be achieved?
                      the sample output is like:
                      fname lname job_id

                      Tom Lee 10
                      Ann Jackstat 6

                      Please reply. this is urgently required.
                      • 8. Re: DISTINCT and ORDER BY
                        3096
                        You've just got to defne how you want that scenario to be treated. Maybe this would work:
                        SELECT fname, lname
                        FROM employee
                        group by fname, lname
                        Order by max(job_id)
                        Not tested
                        • 9. Re: DISTINCT and ORDER BY
                          609723
                          Thanks David.
                          • 11. Re: DISTINCT and ORDER BY
                            643647
                            I wrote a long explanation for this query and the oracle forum preview window decided to eat it, so I'm just posting the query, which might help some people looking for a SELECT DISTINCT approach that retains a sort order and takes the first value for the various grouping columns. In this case, my goal is to get a distinct list of agencies (agname):
                            select distinct
                                  first_value(program_category_desc) over (partition by agname
                                    order by program_category_desc, program_subcategory_desc, progtypename, agname) cat,
                                  first_value(program_subcategory_desc) over (partition by agname
                                    order by program_category_desc, program_subcategory_desc, progtypename, agname) sub,
                                  first_value(progtypename) over (partition by agname
                                    order by program_category_desc, program_subcategory_desc, progtypename, agname) ptype,
                                  first_value(agname) over (partition by agname
                                    order by program_category_desc, program_subcategory_desc, progtypename, agname) ag
                                from R_CONCERTS_REPORT
                                order by first_value(program_category_desc) over (partition by agname
                                    order by program_category_desc, program_subcategory_desc, progtypename, agname),
                                  first_value(program_subcategory_desc) over (partition by agname
                                    order by program_category_desc, program_subcategory_desc, progtypename, agname),
                                  first_value(progtypename) over (partition by agname
                                    order by program_category_desc, program_subcategory_desc, progtypename, agname),
                                  first_value(agname) over (partition by agname
                                    order by program_category_desc, program_subcategory_desc, progtypename, agname);
                            • 12. Re: DISTINCT and ORDER BY
                              643647
                              To get lag/lead values for this list:
                              select agency_key, lag(agency_key) over (order by 1), lead(agency_key) over (order by 1)
                              from
                              (<query above>)