Link to home
Create AccountLog in
Avatar of Allen Pitts
Allen PittsFlag for United States of America

asked on

Order by date desc

Good morning expert,

Working on query that will select dollar amount above
5k on table with the most recent date. It was thought that if I listed the
amounts >5000 and sorted by date and then selected the amount
in the first row that the query would select the most recent date.

But the table, copied herewith below, shows the dates are sort
of jumbled up. Why are the dates not listed in order?

Thanks

Allen in Dallas



Code
SELECT TAU_UID, Key_TAU_UID,SH_MR_HIGH_DOLLAR_Amount, SH_MR_HIGH_DOLLAR_date
from
SH_MR_High_Dollar
SH_High_Dollar_Threshold
INNER join SH_High_Dollar_Threshold on KEY_TAU_UID =TAU_UID
where SH_MR_HIGH_DOLLAR_amount > 5000
-- and rownum = 1
order by SH_MR_HIGH_DOLLAR_date desc


Results
tau_uid      key_tau_uid   sh_mr_high_dollar_amount           sh_mr_high_dollar_date
110      110               10000                               10/14/2012 00:00:00
110      110               20000                               10/12/2012 00:00:00
207      207               15000                               10/17/2012 00:00:00
207      207               10000                               10/15/2012 00:00:00
ASKER CERTIFIED SOLUTION
Avatar of sventhan
sventhan
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
or

order by to_date TO_DATE(SH_MR_HIGH_DOLLAR_date, 'DD-MON-YYYY HH24:MI:SS')) desc
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Is SH_MR_HIGH_DOLLAR_date a varchar2 or an actual date column?

If it is a character and in the format you posted, the to_date posted above will not work.  The format string needs to match the format in the database.

The results you posted doesn't appear to be sorted by a string so I really don't think this is the issue.

I cannot see how the sample data is ordered.

Try executing your select from sqlplus.  It might be the tool you are using that is over-riding the select order by.
Can you post the data and table structures that produced those results?