Quote:
Originally Posted by
methyl
If this is to be a regular process it is very complex because of the alphabetic month abbreviations and the absence of the year in the data.
Surely this would be better as a full blown SQL program which could deduce the year, translate the data into a sortable format, sort to order and run the required SQL.
Hi methyl,
or I can select another one column also with that partition name as below.
SQL> select partition_name,high_value from user_tab_partitions where table_name='USER_AUDIT';
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
PARTMAX MAXVALUE
SAS_AUDIT_NOV02 TO_DATE(' 2009-11-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SAS_AUDIT_NOV08 TO_DATE(' 2009-11-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SAS_AUDIT_NOV14 TO_DATE(' 2009-11-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SAS_AUDIT_OCT27 TO_DATE(' 2009-10-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
is there any way to sort with the help of high value column?
---------- Post updated 11-11-09 at 02:21 AM ---------- Previous update was 11-10-09 at 01:26 PM ----------
hi Folks,
Can anybody help me to sort the below output?
PARTITION_NAME HIGH_VALUE
------------------------------ --------------------------------------------------------------------------------
SAS_AUDIT_NOV02 TO_DATE(' 2009-11-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SAS_AUDIT_NOV08 TO_DATE(' 2009-11-08 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SAS_AUDIT_NOV14 TO_DATE(' 2009-11-14 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SAS_AUDIT_OCT27 TO_DATE(' 2009-10-27 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
This output will be stored in a file. From that I can print the date only as below by using cat output.lst|awk '{print $3}'|sort -n
2009-10-27
2009-11-02
2009-11-08
2009-11-14
Now I need to take the partition_ name of oldest date. In this scenario, the oldest date is 2009-10-27 and corresponding partition_name is SAS_AUDIT_OCT27. So I need the output as below.
Required output:
============
SAS_AUDIT_OCT27