Hi Mark,
Here is what I tried and found.  I have table with date type field (it re
ally
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 thi
rd
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 a
ll
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 gro
up, it
will group by rackID.....) 

That's why you don't get group by date, since your date field listed in t
he
last of the group by clause. If you want group by date, put it first.
=2E...
GROUP BY dateInvRecv, positionID, partID, rackID, isChildOf, faulty,
lotNumber
=2E..

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


Li li,

SELECT     SUM(qtyReserved) AS qtyTtlAloc, positionID, partID, rackID,
isChildOf, faulty, lotNumber, dateInvRecv
FROM         dbo.tblOrder_InventoryAllocation
GROUP BY positionID, partID, rackID, isChildOf, faulty, lotNumber,
dateInvRecv

Thanks a lot. You will need to create this table with all these fields,
among them, only faulty and isChildOf are int, others aree nvarchar. 


Another clue here is that all other fields grouped fine except the
dateInvRecv. And this is the only one with the "/"; though I can't imagin
g
how that could make any difference.

Li li such a lovely name, are you by any chance Chinese? Sorry if I guess
ed
wrong.

Mark

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


Mark, 

What is your SQL statement?
Could you type it here?  I will check it for you from my database tonight
=2E

Li Li

"Mark.Chen" <Mark.Chen@s...> wrote:
Li li,

That is what I thought. So I went into the table and check them. I even c
ut
and paste from one to another. Did not work.

Mark

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


I guess the value are actually different even stored date in xx/xx/xxx
format.
 The seconds that not display may different.  Check them?

Li li



"Mark.Chen" <Mark.Chen@s...> wrote:
Hi,

I have encountered the weirdest problem in SQL.

I have a table with a field to store date in xx/xx/xxxx format. The data
type is nvarchar.

I was attempting to GROUP BY this field. Then I found that the result
separate the records with same date! I tried again with SELECT DISTINCT, 
it
still does the samething: same date, different records!

All other fields in this table grouped fine except for this one. This is
driving me crazy!

Anyone has a remedy to this?

Mark


To unsubscribe send a blank email to leave-sql_language-512781U@p...
com



____________________________________________________________________
Get free email and a permanent address at http://www.amexmail.com/?A=3D1