• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 979
  • Last Modified:

Oracle Max Function

A column contains possible values like 'A', 'B', 'C'...'Z', 'AA', 'AB'... until 'ZZ'. This type of column is present in a lot of tables...

oracle MAX function returns 'Z' as greater the 'AA'. I want a function which returns 'AA' to be greater than 'Z'

Basically i am trying to achieve something like  
               SELECT MAX_CUSTOM(column_name) FROM TABLE_NAME WHERE...;;

If it is single table i could write my own function but i need to use this function on different tables.

How to achieve this? Any pointers would be greatful.

0
sivi_3883
Asked:
sivi_3883
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you have to return longer string first...

 
select column_name from  ( select column_name, row_number() over ( order by length(column_name) desc, column_name desc ) rn from yourtable ) where rn = 1 

Open in new window


and you could hence return the full row, actually...
 
select * from  ( select t.* , row_number() over ( order by length(column_name) desc, column_name desc ) rn from yourtable t ) where rn = 1 

Open in new window

0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
you can pick the one with the one which has more length right in that case right ?
0
 
awking00Commented:
What is the rest of your where clause?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
POracleCommented:
what abt this simple solution with simple logic

SQL> SELECT * FROM TEST ORDER BY LENGTH(A) DESC,A DESC;

A
-----------------------------------------------------------
ZZ
BB
AA
Z
B
A
0
 
POracleCommented:
need max?
SQL> SELECT * FROM (SELECT * FROM TEST ORDER BY LENGTH(A) DESC,A DESC)
WHERE ROWNUM<2;

0
 
clark4presCommented:
Not sure what the goal is, but this could work with whatever additional WHERE clauses are needed.  They would need to be added to the sub select that returns the MAX LENGTH.

SELECT MAX(a.column_name)
FROM table_name a
WHERE LENGTH(a.column_name) = (SELECT MAX(LENGTH(a2.column_name))
                                                           FROM table_name a2)  
0
 
ianmills2002Commented:
Try

SELECT MAX(LPAD(COLUMN_NAME, 2, ' ') FROM TABLE_NAME ....


This puts leading spaces in front of the single character fields. The Space character comes before any letters so AA will now appear after Z.

You may need to wrap the trim function around the MAX value to remove the leading spaces added to get the sort right.

Similarly, if you need 3 characters, then use LPD with 3 instead of 2.

Hopefully this helps.
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now