CODEWITHSUNDEEP
sqlcoldfusion
Jason Congerton
Jason Congerton

Reputation: 830

Getting the highest values from the database

I am using the following query to grab delivery types from an SQL database, the query gets all delivery types associated with the list of products in the shopping cart. The query could return a minimum of two delivery types for one product or many more for multiple products within the cart. However i just need the two most expensive delivery cost to be displayed.

E.G. Product 1 has Std Delivery £9.99 AND 2 Man delivery £29.99 Product 2 has Std Delivery £18.95 AND 2 Man Delivery £39.99

I need to display to the screen the delivery types for product 2. Can i use the max function or will that only return the highest delivery price?

Query being used:

<cfquery name="getDeliveryType" datasource="#application.dsn#">
SELECT 
  uid_pdelopt,
  txt_pdelopt_type,
  mon_pdelopt_cost,
  uid_pdelopt_prodid,
  txt_pdelopt_detail,
  bit_pdelopt_active,
  bit_pdelopt_selected,
  uid_pdelopt_webid,
  uid_pdelopt_typeid,
  mon_pdelopt_actcost
    FROM dbo.tbl_product_deliveryopt
    WHERE bit_pdelopt_active=<cfqueryparam cfsqltype="cf_sql_bit" value="yes">
   AND uid_pdelopt_prodid IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.uid_pdelopt_prodid#" list="yes">)
</cfquery>

Any pointers would be appreciated.

Jason

Upvotes: 0

Views: 140

Answers (2)

Stelian Matei
Stelian Matei

Reputation: 11623

Let's try this to see if it works:

SELECT TOP 2
 txt_pdelopt_type, MAX(mon_pdelopt_actcost) AS myCost, OTHER_FIELD

FROM dbo.tbl_product_deliveryopt 

WHERE bit_pdelopt_active=<cfqueryparam cfsqltype="cf_sql_bit" value="yes"> AND uid_pdelopt_prodid IN (<cfqueryparam cfsqltype="cf_sql_integer" value="5,49" list="yes">) 

GROUP BY uid_pdelopt_prodid, txt_pdelopt_type, OTHER_FIELD

ORDER BY myCost DESC

Change OTHER_FIELD with eventual other fields you might need.

Upvotes: 0

Stelian Matei
Stelian Matei

Reputation: 11623

You can order by cost DESC and limit the results to 2

<cfquery name="getDeliveryType" datasource="#application.dsn#">
SELECT TOP 2
  uid_pdelopt,
  txt_pdelopt_type,
  mon_pdelopt_cost,
  uid_pdelopt_prodid,
  txt_pdelopt_detail,
  bit_pdelopt_active,
  bit_pdelopt_selected,
  uid_pdelopt_webid,
  uid_pdelopt_typeid,
  mon_pdelopt_actcost
    FROM dbo.tbl_product_deliveryopt
    WHERE bit_pdelopt_active=<cfqueryparam cfsqltype="cf_sql_bit" value="yes">
   AND uid_pdelopt_prodid IN (<cfqueryparam cfsqltype="cf_sql_integer" value="#arguments.uid_pdelopt_prodid#" list="yes">)

    ORDER BY mon_pdelopt_cost DESC
</cfquery>

Upvotes: 1

Related Questions