Kani Kani - 4 months ago 11
SQL Question

SQL - Get max date from dd/mm/yyyy formated column

I have db table which have column name

STATUSDATE
. Type of this column is varchar2 and that column already have data in dd/mm/yyyy format. And i want get the reason date(max date). I used max() method for thi but it not give the correct result
as example consider following dates

31/08/2014

01/09/2016

after using max(STATUSDATE) the result is 31/08/2014. I'm using oracle db.

I'm try to use following quarry but since above problem its give incorrect results

SELECT * FROM MY_DB.MY_TABLE t
inner join (
select CLIENTNAME, max(STATUSDATE) as MaxDate
from FROM MY_DB.MY_TABLE
group by CLIENTNAME
) tm on t.CLIENTNAME = tm.CLIENTNAME and t.STATUSDATE = tm.MaxDate


please can anyone suggest proper way to do this
Thank You

Answer

Moral: Don't store dates as strings. Databases have built-in types for a reason.

So, convert to a proper date and take the max, but you don't need a JOIN for this:

select t.*
from (select t.*,
             rank() over (partition by client_name
                          order by to_date(statusdate, 'DD/MM/YYYY') desc
                         ) as seqnum
      from my_db.my_table t
     ) t
where seqnum = 1;