At 12:36 PM 31/10/2004 -0500, you wrote:

>Hello all,
>
>I have a table with the following relevant fields:
>
>ItemID // Integer
>ProductDescription // Varchar
>OrderID // Integer
>
>This is the current statement that I use:
>
>SELECT
> Count(ITEMS.PRODUCTDESCRIPTION) AS CountItems,
> Sum(ITEMS.ITEMPRICE) AS TotalItems,
> ITEMS.ORDERID, ITEMS.PRODUCTDESCRIPTION,
>FROM ITEMS
>GROUP BY
> ITEMS.ORDERID,
> ITEMS.PRODUCTDESCRIPTION,
> HAVING ITEMS.ORDERID = :OrdID;
>
>What I need to do is pull both pull the records out as they were entered
>into the database, but also group them by productdescription which forces a
>sort on the productdescription. I can't use ItemID to ORDER BY (which is
>the record id for a particular orderID) because I am forced to use to use it
>in the GROUP BY and since the value is unique per record....
>
>Any suggestions on a work around I might try?

The reason for a grouping like this would be to roll together multiple
instances of the same ProductDescription contained in the same
order. Therefore, by grouping them, you couldn't get them in arrival
sequence, could you? In any case, if you don't have a timestamp to work
with, there is no way to determine their absolute arrival sequence. This is
going to be the case, regardless of how you go about getting the set.

btw, in grouped queries, don't use HAVING to replace a WHERE
clause. Although it "works", it causes all of the records to be fetched
into the intermediate ungrouped set. Restrict the use of HAVING for
setting conditions based on aggregate expressions, e.g.

HAVING COUNT(ITEMS.DESCRIPTION) > 0
or
HAVING SUM(ITEMS.ITEMPRICE) < 100.00

OK, let's suppose you do have a timestamp on ITEMS (let's call it
TSTAMP). Would this work for you?


SELECT Count(PRODUCTDESCRIPTION) AS CountItems,
Sum(ITEMPRICE) AS TotalItems,
Min(TSTAMP) AS Sequence.
ORDERID, /* really needed? */
PRODUCTDESCRIPTION,
FROM ITEMS
WHERE ORDERID = :OrdID;

GROUP BY
ORDERID /* really needed? *?
PRODUCTDESCRIPTION
ORDER BY 3

If you don't have an equivalent of TSTAMP, about all that's left is to take
a punt that the physical ordering of the records on disk is the same as
arrival order (not an absolute given!) and use the RDB$DB_KEY:

SELECT Count(PRODUCTDESCRIPTION) AS CountItems,
Sum(ITEMPRICE) AS TotalItems,
Min(RDB$DB_KEY) AS Sequence.
ORDERID, /* really needed? */
PRODUCTDESCRIPTION,
FROM ITEMS
WHERE ORDERID = :OrdID;

GROUP BY
ORDERID /* really needed? *?
PRODUCTDESCRIPTION
ORDER BY 3

./heLen