SQL Grouping / Sorting Question

  • Thread starter Thread starter Mike Thomas
  • Start date Start date
M

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
 
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;
 
Back
Top