SQL "Group By" doesn't sort

[mcoupal]

On a win98 machine running VFP6, I am getting results on a date field in this sql out of order:

sele date from timehist grou by date (Date = a date field)

settings:
set cent on
set stri to 0
(I even made the time/date format MM/dd/yyyy in the control panel)
The table has index, but isn't currently ordered, and I tried reindexing.

Yet, when I try this on my win2000 machine with the same settings on a table of the same structure, the results are ordered. Both machines have the service pack 2 update.

Most of the records are in order but I'll see this:
04/09/2002
04/11/2002
04/08/2002
04/12/2002
04/10/2002

Has anyone seen this?

 

[mcoupal]

Oops. I meant service pack 5 (not two).

 

[youwannawhat]

Shouldn't it be 'order by date'?

 

[mcoupal]

I want to do a group by so I only get one record per day. I've always seen group by automatically sort as well.

 

[DSummZZZ]

GROUP BY doesn't necessarily order the records, it only produces one aggregate record for the column specified. You need to add an ORDER BY statement to ensure it is sorted. GROUP BY may default to ordering the fields, but you may have a flakey index file which could cause the query to be generated differently on the NT box. You would need to run the query on the same files to rule out any quirks with the different OS's.

Dave S.

 

[EzLogic]

try this:

Select Date from TimeHist where Date = MyField Order by Date Group by Date

that should do it! Tekno
Wireless Toyz
Ypsilanti, Michigan

 

[EzLogic]

oops in the above statment it should read:

Where MyField = DateToCheck
Tekno
Wireless Toyz
Ypsilanti, Michigan