ORDER BY [DATE] producing unordered result

Question

Post reply

ORDER BY [DATE] producing unordered result

jay.jose

Say Hey Kid

Points: 683
More actions
July 20, 2009 at 4:59 pm

#202692

Hi,
I'm having a problem with a query. I need to order a query result by the amount then by date. I have the following at the end of my query:
ORDER BY
AMOUNT DESC,
[DATE] ASC
the amount did order properly but the date is just messed up...here's a sample result i got
DATE AMOUNT
1992-01-31 00:00:00.00020.00
1991-05-13 00:00:00.00020.00
1991-03-14 00:00:00.00020.00
1990-12-08 00:00:00.00020.00
2007-12-27 00:00:00.00020.00
1992-05-28 00:00:00.00015.00
1992-04-18 00:00:00.00015.00
1991-06-17 00:00:00.00015.00

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

The date looks OK to me. Why do you think the date is "messed up".

jay.jose Say Hey Kid Points: 683 More actions · Answer 2

Michael Valentine Jones (7/20/2009)
The date looks OK to me. Why do you think the date is "messed up".

DATE AMOUNT

1992-01-31 00:00:00.000 20.00

1991-05-13 00:00:00.000 20.00

1991-03-14 00:00:00.000 20.00

1990-12-08 00:00:00.000 20.00

2007-12-27 00:00:00.000 20.00 <--- this should be on top of the list since its dated 2007

1992-05-28 00:00:00.000 15.00

1992-04-18 00:00:00.000 15.00

1991-06-17 00:00:00.000 15.00

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

Do it this way to get the date in decending order within amount.

ORDER BY

AMOUNT DESC,

[DATE] DESC

jay.jose Say Hey Kid Points: 683 More actions · Answer 4

here are other result i'm getting

ORDER BY

AMOUNT DESC,

[DATE] DESC

1991-12-17 00:00:00.000 15.00

1992-07-18 00:00:00.000 15.00

2001-04-17 00:00:00.000 15.00

2000-09-06 00:00:00.000 15.00 <-- from 1991, 1992, 2001 then back to 2000

1983-02-21 00:00:00.000 11.67

1981-10-24 00:00:00.000 11.67

1985-02-16 00:00:00.000 11.67

1982-06-23 00:00:00.000 11.67

1981-02-24 00:00:00.000 11.67

1980-06-27 00:00:00.000 11.67

1979-10-29 00:00:00.000 11.67

1979-03-01 00:00:00.000 11.67

1985-10-16 00:00:00.000 11.67

1986-06-15 00:00:00.000 11.63 <--- all the 11.67 are messed up also

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

What is the data type of the AMOUNT column?

Could it be a MONEY datatype that you are stripping the last 2 digits from to show with 2 decimal places?

jay.jose Say Hey Kid Points: 683 More actions · Answer 6

Yup, it is a MONEY datatype.. Thanks for the input i'll look into it ^_^

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Can you post the entire query?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

jay.jose Say Hey Kid Points: 683 More actions · Answer 8

Hi,

I saw where the problem is..there are two [DATE] fields on the table (i guess 1 is the actual date and the second is the date when they encoded the transaction) and most of it has the same date but I noticed that there are some that have different values on it. And thats what has been causing the problem during sort. Thanks for all the time and help. I'll be posting more problems as they arise ^_^

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

jay.jose (7/21/2009)
I'll be posting more problems as they arise ^_^

I recommend you study the article at the first link in my signature lines below before you do that. You'll be amazed at how much it helps.;-)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

phanish81 Newbie Points: 5 More actions · Answer 10

I think the problem is because of combination of Dates which have both years 2000 and 1900. i am having the same problem,trying for a solution,will let you know if i find a solution.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 11

phanish81 (4/7/2010)
I think the problem is because of combination of Dates which have both years 2000 and 1900. i am having the same problem,trying for a solution,will let you know if i find a solution.

Please read last jay.jose posting, he already found the problem. It was an interface issue.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.