Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

ORDER BY [DATE] producing unordered result Expand / Collapse
Author
Message
Posted Monday, July 20, 2009 4:59 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 2, 2009 3:59 PM
Points: 13, Visits: 31
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.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
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
Post #756244
Posted Monday, July 20, 2009 5:04 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:39 AM
Points: 3,105, Visits: 11,494
The date looks OK to me. Why do you think the date is "messed up".
Post #756246
Posted Monday, July 20, 2009 5:08 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 2, 2009 3:59 PM
Points: 13, Visits: 31
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
Post #756253
Posted Monday, July 20, 2009 5:18 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:39 AM
Points: 3,105, Visits: 11,494
Do it this way to get the date in decending order within amount.
ORDER BY
AMOUNT DESC,
[DATE] DESC

Post #756258
Posted Monday, July 20, 2009 5:19 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 2, 2009 3:59 PM
Points: 13, Visits: 31
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
Post #756259
Posted Monday, July 20, 2009 5:24 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 12:39 AM
Points: 3,105, Visits: 11,494
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?



Post #756261
Posted Monday, July 20, 2009 5:32 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 2, 2009 3:59 PM
Points: 13, Visits: 31
Yup, it is a MONEY datatype.. Thanks for the input i'll look into it ^_^
Post #756264
Posted Tuesday, July 21, 2009 2:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 3:43 PM
Points: 39,866, Visits: 36,206
Can you post the entire query?


Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #756371
Posted Tuesday, July 21, 2009 8:24 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, December 2, 2009 3:59 PM
Points: 13, Visits: 31
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 ^_^
Post #756624
Posted Tuesday, July 21, 2009 6:24 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 35,215, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #757093
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse