CODEWITHSUNDEEP
coldfusionsql-server-2012coldfusion-11
volume one
volume one

Reputation: 7533

How to order a query by values in different columns

I have a recordset named rsProductClass that is returned from a table in the database. It is a very simple SELECT * FROM Table WHERE ProductID = {ID Value Here} and the table is like this:

ProductID | UPPERTIER | LOWERTIER | NATIER  | OTHERTIER
1           20          60          10        10
2           10          90          NULL      NULL
3           NULL        40          NULL      5

The table may or may not have a value for each of the various tiers.

What I want to do is show to the user which column has the highest value and what the name of that column is. So for example, if you were looking at ProductID 2, then the page should display "This is likely to be a LOWERTIER product"

I need to sort the rsProductClass query in such a way that it returns me a list of columns in that query ordered by the value in each column. I want to treat the NULL values as zeros.

I tried to mess about with doing valuelist() and some ArrayToList() type functions but it crashes on the NULL values. Say I add columns to an array, and then use ArraySort() to get them in some kind of order, I'll get an error saying something like "Position 1 is not numeric" because it has a NULL value.

Is this something that can be done by ColdFusion? I suppose its some sort of pivoting of the recordset which is beyond my ability.

Upvotes: 0

Views: 259

Answers (3)

abbottmw
abbottmw

Reputation: 754

I had to do something similar to this recently and looked into UNPIVOT in SQL Server. Going with the suggestion to Unpivot your query like David said, you could do something like this. This doesn't add RANK column, but it does order the values.

SELECT ProductID, Tier, TierValue
FROM
(SELECT ProductID, ISNULL(UpperTier,0) UpperTier, ISNULL(LowerTier,0) LowerTier, ISNULL(NaTier,0) NaTier, ISNULL(OtherTier,0) OtherTier
FROM products) p
UNPIVOT
(TierValue FOR Tier IN 
(UpperTier, LowerTier, NaTier, OtherTier)
)AS unpvt
ORDER BY ProductID, TierValue Desc

SQL FIDDLE

Upvotes: 2

David Faber
David Faber

Reputation: 12486

If you're willing to unpivot your query, you might do something like the following. I used COALESCE() instead of ISNULL() (either one works in this situation, but COALESCE() is the ANSI standard). The column tier_rank will give the rank of the given tier -- that is, the tier with the highest value will have a rank of 1. If there are two tiers that both have the highest value, then both will have a value in tier_rank of 1 (this is why you would use RANK() instead of ROW_NUMBER() -- you could also use DENSE_RANK() if it better fits your requirements):

SELECT p1.product_id, p1.tier_name, p1.tier_value
     , RANK() OVER ( PARTITION BY p1.product_id ORDER BY p1.tier_value DESC ) tier_rank
  FROM (
  SELECT product_id, 'UPPERTIER' AS tier_name
       , COALESCE(uppertier, 0) AS tier_value
    FROM products
   UNION ALL
  SELECT product_id, 'LOWERTIER' AS tier_name
       , COALESCE(lowertier, 0) AS tier_value
    FROM products
   UNION ALL
  SELECT product_id, 'NATIER' AS tier_name
       , COALESCE(natier, 0) AS tier_value
    FROM products
   UNION ALL
  SELECT product_id, 'OTHERTIER' AS tier_name
       , COALESCE(othertier, 0) AS tier_value
    FROM products
) p1

Please see SQL Fiddle demo here.

It might be possible to re-pivot the above unpivoted query, but I must admit my attempts at doing so failed.

Upvotes: 2

Tomalak
Tomalak

Reputation: 338108

Something like this would work:

<cfquery name="tiers" datasource="...">
    SELECT ProductID, UPPERTIER VALUE, 'UPPERTIER' TIER
    WHERE  UPPERTIER IS NOT NULL
    UNION 
    SELECT ProductID, LOWERTIER VALUE, 'LOWERTIER' TIER
    WHERE  LOWERTIER IS NOT NULL
    UNION
    SELECT ProductID, OTHERTIER VALUE, 'OTHERTIER' TIER
    WHERE  OTHERTIER IS NOT NULL
    UNION
    SELECT ProductID, NATIER VALUE, 'NATIER' TIER
    WHERE  NATIER IS NOT NULL
    ORDER BY ProductID, VALUE
</cfquery>

<cfset productGroup = StructNew()>

<cfoutput query="tiers" group="ProductID">
  <cfset productGroup[ProductID].TIER = TIER>
  <cfset productGroup[ProductID].VALUE = VALUE>
</cfoutput>

<cfdump var="#productGroup#">

Starting with ColdFusion 10 you can use <cfloop query="..." group="...">, before that <cfoutput> must be used.

Upvotes: 2

Related Questions