There's no reason why you shouldn't be able to get it sorted by the original
query.
Did you try "ORDER BY AmountSold" or "ORDER BY 2"?
What table(s) are Status and ShopUID on? What does Status=2 mean?
Nick
-----Original Message-----
From: Aidan Whitehall [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 28, 2000 3:56 PM
To: CF-Talk (E-mail)
Subject: Desperately need help - sorting a QueryNew()
This is an urgent problem... thanks for any help offered.
I've got a query that retreives product names and quantities sold. Having
spent ages trying to get the query to return the recordset sorted by the
amount sold (desc), I've given up (I'm trying to output the top 5 items sold
on a website).
So far, this query returns the recordset...
<!--- Get the orders --->
<cfquery datasource="#attributes.DataSource#" name="GetOrders">
SELECT PurchaseItem.Name,
SUM(PurchaseItem.Quantity) AS AmountSold,
PurchaseStatus.Status
FROM (Purchase INNER JOIN PurchaseItem ON Purchase.PurchaseID =
PurchaseItem.PurchaseID)
LEFT JOIN PurchaseStatus ON PurchaseItem.PurchaseItemID =
PurchaseStatus.PurchaseItemID
WHERE ShopUID = '#Session.ShopUID#'
AND Status = 2
GROUP BY PurchaseItem.Name, PurchaseStatus.Status
ORDER BY SUM(PurchaseItem.Quantity), PurchaseItem.Name
</cfquery>
I figured I'd create a new query, sort it by the QuantitySold column using
ArraySort (Ben, CFWACK p920 says you can use Array functions on Queries)
like this:
<CFSET variables.Sales = QueryNew("Quantity, ProductName")>
<CFOUTPUT QUERY="GetOrders">
<CFSET variables.Result = QueryAddRow(variables.Sales)>
<CFSET variables.Result = QuerySetCell(variables.Sales, "Quantity",
AmountSold)>
<CFSET variables.Result = QuerySetCell(variables.Sales, "ProductName",
Name)>
</CFOUTPUT>
<CFSET variables.Sales = ArraySort(variables.Sales[Quantity], "numeric",
"desc")>
but that throws an error:
Error resolving parameter QUANTITY
The error occurred while evaluating the expression:
variables.Sales = ArraySort(variables.Sales[Quantity], "numeric", "desc")
Can anyone please help? Thanks if you can.
--
Aidan Whitehall <[EMAIL PROTECTED]>
Netshopper UK Ltd
Advanced Web Solutions & Services
http://www.netshopperuk.com/
Telephone +44 (01744) 648650
Fax +44 (01744) 648651
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
**********************************************************************
Information in this email is confidential and may be privileged.
It is intended for the addressee only. If you have received it in error,
please notify the sender immediately and delete it from your system.
You should not otherwise copy it, retransmit it or use or disclose its
contents to anyone.
Thank you for your co-operation.
**********************************************************************
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
