order by confusion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

order by confusion

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-01-20 : 15:51:41

I cant find an answer for this one if anyone could please help that would be great :

this statement gives me the following resultset (where the highlighted red is the sale date column):


select * from itemsales_test where saledate = '15/01/2007'

saledate
26399595 2007-01-15 00:00:00 105 1951 6.32 3 0 .000 N 3 1.00 3.00 1
26399596 2007-01-15 00:00:00 105 56490 2.98 2 0 1.620 N 3 1.00 2.00 1
26399671 2007-01-15 00:00:00 105 100786 9.98 2 0 6.186 N 3 6.00 .33 35858
26399672 2007-01-15 00:00:00 105 100794 .99 1 0 .508 N 3 8.00 .13 66429C
26399673 2007-01-15 00:00:00 105 100798 .39 1 0 .325 N 3 24.00 .04 23723
26399674 2007-01-15 00:00:00 105 100802 7.16 4 0 4.030 N 3 12.00 .33 66429C

but this



Select * from itemsales _test  order by saledate desc

gives me this:
SALEDATE

26284233 2007-01-13 00:00:00 103 100052 4.49 1 0 2.005 N 2 6.00 .17 13540
26284234 2007-01-13 00:00:00 103 100067 1.76 2 0 1.060 N 2 12.00 .17 11936
26284235 2007-01-13 00:00:00 103 100080 1.55 1 0 .988 N 2 15.00 .07 20108
26284236 2007-01-13 00:00:00 103 100225 2.18 2 0 1.438 N 2 12.00 .17 13816
26284237 2007-01-13 00:00:00 103 100239 .39 1 0 .249 N 2 36.00 .03 22790
26284238 2007-01-13 00:00:00 103 100260 .31 1 0 .162 N 2 48.00 .02 34160

I was expecting the

select * from itemsales _test  order by saledate desc

statement to give me the 15/01/2007 first in the resultset .

why is this ?

when

select max(saledate) from itemsales_test

also gives me '2007-01-15'

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-20 : 16:33:21

Is column saledate a datetime column or a character column?

CODO ERGO SUM

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-01-20 : 16:55:18

it is a smalldatetime column Michael.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-20 : 18:39:55

What's the status of your index on the column?

Peter Larsson
Helsingborg, Sweden

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2007-01-20 : 19:32:56

Hi Peso, I have a non clustered index on the saledate column and a clustered index on a saleid column.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-21 : 05:15:12

Rebuild your index and see if that makes a difference.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2007-01-21 : 10:09:24

If there is a chance that there is a corruption I would recommend doing a DBCC CHECKDB (with the NO Informational Messages option) before rebuilding the index - just show you know whether there is a real problem, or not, before the Rebuild silently fixes it!

Kristen

jayp369
Starting Member

26 Posts

Posted - 2007-01-21 : 12:00:25

This happened to me, and rebuilding the index fixed the problem. i believe the index was corrupted, and i also set up a weekly job to automatically rebuild the index.

Jay

Kristen
Test

22859 Posts

Posted - 2007-01-21 : 19:17:47

Trouble is that a corruption might be caused by faulty hardware, and therefore "just fixing the problem" hides the cause - which may then [e.g. for hardware faults] go on to trash the whole database

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources