SQL Grouping / Sorting Question

[Mike Thomas]

The query below is giving me a 'not part of an aggregate expression' error
for the ORDER BY VID.CategoryKey line below:

SELECT Sum(VIDetail.InvUnitPrice) AS UnitPrice, First(VID.QtyInvoiced) AS
QtyInvoiced
FROM VID
GROUP BY VID.ReceiptItemKey
ORDER BY VID.CategoryKey;

I am trying to arrange this query so that before the grouping is done, the
records are ordered by the category key so that the "First(VIDl.QtyInvoiced)
AS QtyInvoiced" will always return the quantity from the correct record; eg
the category with the lowest ID #.

Is there a way to rewrite this query without having to split it into 2
queries?

Thanks
Mike Thomas

 

[Ken Snell [MVP]]

Perhaps this:

SELECT Sum(VIDetail.InvUnitPrice) AS UnitPrice,
First(SELECT TOP 1 V.QtyInvoiced AS QI
FROM VID AS V WHERE V.ReceiptItemKey =
VID.ReceiptItemKey ORDER BY V.CategoryKey) AS
QtyInvoiced
FROM VID
GROUP BY VID.ReceiptItemKey;

 

[Mike Thomas]

Thanks Ken,

I don't think I would have arrived at that solution.

Mike

 

[Ken Snell [MVP]]

You're welcome. Glad it is working for you!