Hi Tom, a Short question

In this query, I expect column "a" and "b" to have the same value

select table_name,
column_id,
column_name,
first_Value(column_name) over (partition by table_name order by column_id) a,
last_value(column_name) over (partition by table_name order by column_id desc) b from all_tab_columns
where table_name='ALL_ALL_TABLES';

TABLE_NAME COLUMN_ID COLUMN_NAME A B
ALL_ALL_TABLES 1 OWNER OWNER OWNER
ALL_ALL_TABLES 2 TABLE_NAME OWNER TABLE_NAME
...
ALL_ALL_TABLES 51 COMPRESSION OWNER COMPRESSION
ALL_ALL_TABLES 52 DROPPED OWNER DROPPED

but Last_Value (b) seems to be the same than "Column_name". What am I doing wrong?

Thanks in advance.

and we said...
first_Value(column_name) over (partition by table_name order by column_id) a,

that will take the data, break it up by table_name

then, within each TABLE_NAME, sort by column_id

then, return the FIRST_VALUE of column name in the window which is by default (since you didn't say otherwise) going from the CURRENT ROW to UNBOUNDED PRECEDING.

So, the first_value is the first row in the window - always.

last_value(column_name) over (partition by table_name order by column_id desc) b from
all_tab_columns

that will

a) break up by table_name
b) sort by column_id
c) return last row from current window - which by default is the CURRENT ROW and UNBOUNDED PRECEDING....

The last row of the window is always changing - as you progress through the partition.

you either want:

a) the first_value after sorting by column_id - wait you already have that.
b) the last_value with rows between current row and unbounded following.

ops$tkyte%ORA10GR2> select table_name,
2 column_id,
3 column_name,
4 first_Value(column_name) over (partition by table_name order by column_id) a,
5 last_value(column_name) over (partition by table_name order by column_id desc
rows between current row and unbounded following) b
6 from all_tab_columns
7 where table_name='ALL_ALL_TABLES'
8 /

TABLE_NAME COLUMN_ID COLUMN_NAME A
B
------------------------------ ---------- ------------------------------
------------------------------ ------------------------------
ALL_ALL_TABLES 52 DROPPED OWNER
OWNER
ALL_ALL_TABLES 51 COMPRESSION OWNER
OWNER
ALL_ALL_TABLES 50 DEPENDENCIES OWNER
OWNER
ALL_ALL_TABLES 49 CLUSTER_OWNER OWNER
OWNER
ALL_ALL_TABLES 48 MONITORING OWNER
OWNER
ALL_ALL_TABLES 47 SKIP_CORRUPT OWNER
OWNER

ref: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:812101100346616753