Re: Oracle SQL query - Grouping function to take 1st value for non-grouped cols

I looked at this post earlier today and actually started to write a solution but I needed to do some real work (my job) so I had to put it aside. I would like to point out that the data presented looks like the result of a group by rollup due to the "All Jobs" job entry. If so I would expect that it would be better to write the query against the base data rather than the result set. As presented all entries with "All jobs" as a job are excluded from the desired result set.

I have no experience with them but the analytic function set includes a FIRST, LAST, FIRST_ROWS (which I think I have used), and ROW_NUMBER
(definitely used)functions that may be of use. I believe you need the
Enterprise Edition and 8.1.7.0 or higher to have the analytic functions.

If the number of departments is a known, fixed number then you can write a "union all" of a select * from table where department = 'X' and rownum = 1. The problem with this is that the result set is dependent on the manner in which the test data is derived or the order will be wrong. Potentially this could be fixed by making each select a select on an ordered subquery but this solution would be dependend on the desired row for each department always being the first job by alphabetic order which is the case in the sample set. This version is still dependend on a known, fixed number of departments existing.

Now logically if you selected a distinct list of departments you can use this as a driver to then select from the table where the table.department = the distint.department and you select only one row
(rownum = 1). But again you have the order of the raw data potential
problem since by rights a heap table is an unordered collection or rows. If the manner in which the target data is built is under control then if nothing else a sequence generated column could be added to provide ordering.

This does not look like a real-world problem so I wonder if this is a school problem.

I hope these observations and thoughts are helpful. -- Mark D Powell -- Received on Wed Sep 07 2005 - 19:48:48 CDT