How to order a query by values in different columns

Question

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.

What DBMS are you using? This might be something you can do in the initial query. You should at least be able to get `NULL`s as `0`s using `COALESCE()` -- e.g., `SELECT ProductID, COALESCE(uppertier, 0) AS uppertier, etc.` — David Faber, Jan 20 '15 at 01:09
If you're using MSSQL you can do `isNull(column, 0)` to change null values to 0. — Matt Busche, Jan 20 '15 at 01:40
It might not be possible to do what you think you want to do. For example, change record 3 so that the uppertier value is 50. Then explain in what order you want your records to appear and why. — Dan Bracuk, Jan 20 '15 at 03:32
@DavidFaber I'm using SQL Server 2014. I just thought about using isNull(columnname, 0) to replace the NULLs with zeros. I then have to think about how to unpivot the columns so that they are arranged by the value in the field respectively. — volume one, Jan 20 '15 at 11:16
@DanBracuk In that case, UpperTier would have the highest value so that would be first in the list to be selected. If both UpperTier and LowerTier were 50, then ideally I'd want to select both but I'm happy enough to tell the user that "its most likely to be UpperTier" even though its 50/50 — volume one, Jan 20 '15 at 11:19

score 2 · Answer 1 · edited Jan 20 '15 at 15:24

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.

score 2 · Answer 2 · edited Jan 20 '15 at 15:54

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.

@volumeone - If you cannot modify the schema, this is the approach I would use. That said, given the similar column names - and how the data is being used - a more normalized structure might serve you serve better. — Leigh, Jan 20 '15 at 15:51

abbottmw · Answer 3 · 2015-01-20T19:34:33.353

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

How to order a query by values in different columns

3 Answers3