Re: selecting rows for only column

Marianna

If you have Oracle 8.1.6 and over, you can use analytic functions to efficiently do what you are trying to do. Take a look at the following SQL, run it to get your results, and as you have said you are new(ish) to SQL, I'll explain what it all means below.

select ca, cb, cc
from (select first_value(ca) over

(partition by ca order by cb, cc asc) as ca

       ,      first_value(cb) over


                 (partition by ca order by cb, cc asc) as cb


       ,      first_value(cc) over


                 (partition by ca order by cb, cc asc) as cc


       ,      row_number() over


                 (partition by ca order by cb, cc asc) as rn


       from   t)

CA CB CC
-- -- --
p a x
q a x

First thing we have done is to put the main select in an "in-line view". This basically means a select statement in the FROM clause of an SQL statement. This can be used to limit the results that we need to work on while providing an extra layer of complexity to our SQL capabilities.

There are two analytic functions being used in the in-line view - FIRST_VALUE and ROW_NUMBER.

Explanation:-

FIRST_VALUE(ca) OVER
  (PARTITION BY ca ORDER BY cb, cc) AS ca

PARTITION BY ca - this means take my whole resultset from table T, break it into groups of records and these groups will be determined by column CA.

ORDER BY cb, cc - this means WITHIN each CA group, order the associated CB and CC columns in ascending order (ASC is default).

FIRST_VALUE(ca) OVER - from the ordered set of each partitioned CA group, take just the first record.

Notice we've used the same criteria to get CB and CC in the in-line view.

ROW_NUMBER has been used in the in-line view to number the results as they come back.

If we run the in-line view select only, we get the following:-

CA CB CC RN
-- -- -- ----------

p  a  x           1
p  a  x           2
p  a  x           3
p  a  x           4
q  a  x           1
q  a  x           2

The FIRST_VALUE function operates on every row reported. Notice how the row number column (RN) increments for each partition group. When we move to the 'q' partition group , the row number resets to 1 again, as dictated by our partitioning criteria.

Now as we are only interested in the first value, outside the in-line view (i.e. in the main SELECT), we can reference the RN aliased column to limit each partitioned group to just the first record with a WHERE rn = 1 clause.

So there we go ! Hope this helps.

Regards

Adrian Received on Fri Apr 19 2002 - 10:15:26 CEST