This thread just does not die.

Here is my 2 cents worth.

SQL Server has been out for a long time now.  There are so many
applications running with so many developers that I don't think there is
any basic flaw in the way SQL Server is handling your Group By clause.

The issue must be one of understanding and interpretation.  

Understanding the data that is actually in the database, vs. what you
think is in there.

Then interrupting the results for what they really are.

To help us all out, you should be able to use Query Analyzer to issue
your query.  Then using cut and paste, place both the query and the
results here in the email body so we can all look at the data.

Mike

-----Original Message-----
From: Mark.Chen [mailto:Mark.Chen@s...]
Sent: Wednesday, June 13, 2001 6:00 AM
To: sql language
Subject: [sql_language] RE: [sql_language]: problem with grouping

Li li,

Thanks a lot. I use SQL Server.

However, your solution did not work.

I would like to make this issue a little broader. Attempting to get
around
the problem, I actually tried many other methods. What I have concluded
is
that some of the date appeared to be the same to me, but was not
considered
the same to the system!

For example, I have a few records with 6/11/2001. Some of the fields
with
this date failed to be grouped or to be selected using WHERE clause!

It is amazing how I could be bugged with such a seemingly impossible
problem
for days.

Thanks again,

Mark



-----Original Message-----
From: Li Li [mailto:lilinola@u...]
Sent: Tuesday, June 12, 2001 11:10 PM
To: sql language
Subject: [sql_language] Re: [Re: [Re: [problem with grouping]]]


Hi Mark,
Here is what I tried and found.  I have table with date type field (it
really
does not matter), and string, and integer fields.  The group by will do:
sort
the first one in your group by string, then sort the second field in you
group
by string (inside each group you grouped by the first field), and the
third
field in your group by string will sort rows inside the each second
level
sorted goup.  And so on....
For example your result will be first sorted by positionID, and you get
all
same ID in a group (say ID=3D1 in one group and ID=3D2 in one gorup...)
then inside ID=3D1 group, it will group by partID, then inside partID
group,
it
will group by rackID.....)
That's why you don't get group by date, since your date field listed in
the
last of the group by clause. If you want group by date, put it first.
....
GROUP BY dateInvRecv, positionID, partID, rackID, isChildOf, faulty,
lotNumber
...

I think it will work, if not let me know.
Hope it helps.


Li li,