-2

My desired result set is

Red  Purple
 1     5

all on one row.

My query below returns 4 rows.
What would be the syntax to only return 1 row?

DECLARE @BlueBonnet TABLE (
    randarfield FLOAT
    ,confis VARCHAR(10)
    );

INSERT INTO @BlueBonnet (randarfield, confis)
VALUES('0.00', 'red'), ('0.00','red'), ('0.00','blue'), ('0.00','blue')
    , ('0.00','purple'), ('5.00','purple'), ('0.00','orange')
    , ('1.00','red'), ('0.00','blue'), ('0.00','blue')

SELECT Red  = CASE WHEN confis = 'red' THEN MAX(randarfield) ELSE 0 END
    ,Purple = CASE WHEN confis = 'purple' THEN MAX(randarfield) ELSE 0 END
FROM @BlueBonnet
GROUP BY confis
  • 4
    You say that you want the first result, but how are the results supposed to be ordered? Is there a missing IDENTITY column? – Joe Obbish Aug 31 '17 at 23:55
4

Two options here:

In keeping with your MAX:

SELECT
     MAX(CASE WHEN confis = 'red'    THEN randarfield END) AS Red
    ,MAX(CASE WHEN confis = 'purple' THEN randarfield END) AS Purple
FROM @BlueBonnet

But if you really want the first (not the max):

SELECT
 (SELECT TOP 1 randarfield FROM @BlueBonnet WHERE randarfield>0 AND confis = 'red'   ) Red
,(SELECT TOP 1 randarfield FROM @BlueBonnet WHERE randarfield>0 AND confis = 'purple') Purple

Though depending on your definition of "first" you would need to add an ORDER BY ... to the two subqueries for whatever column you order by to decide what is first.

2

As many people have already mentioned, it depends on if you need The First, The Min or The Max.

Here is a solution using IDENTITY, better test data, PIVOT and ROW_NUMBER():

DECLARE @BlueBonnet TABLE (
    ID INT IDENTITY (1,1) --I added an IDENTITY field!
    ,randarfield FLOAT
    ,confis VARCHAR(10)
    );

INSERT INTO @BlueBonnet (randarfield, confis)
VALUES('0.00', 'red'), ('0.00','red'), ('0.00','blue'), ('0.00','blue')
    , ('0.00','purple'), ('5.00','purple'), ('0.00','orange')
    , ('1.00','red'), ('0.00','blue'), ('0.00','blue')
    , ('3.00','red'), ('2.00','purple') --I added these values!

--The First non-zero randarfield per confis
SELECT *
FROM (
    SELECT confis, randarfield
    FROM (
        SELECT *
            ,ROW_NUMBER()OVER(PARTITION BY confis ORDER BY ID) AS TheFirst
        FROM @BlueBonnet
        WHERE randarfield > 0
        ) Firsts
    WHERE TheFirst = 1
    )Results
PIVOT (MIN(randarfield) FOR confis IN([red],[purple])) AS pvt;

--The Min non-zero randarfield per confis
SELECT *
FROM (
    SELECT confis, MIN(randarfield) AS TheMin
    FROM @BlueBonnet
    WHERE randarfield > 0
    GROUP BY confis
    ) Results
PIVOT (MIN(TheMin) FOR confis IN([red],[purple])) AS pvt;

--The Max non-zero randarfield per confis
SELECT *
FROM (
    SELECT confis, MAX(randarfield) AS TheMax
    FROM @BlueBonnet
    WHERE randarfield > 0
    GROUP BY confis
    ) Results
PIVOT (MIN(TheMax) FOR confis IN([red],[purple])) AS pvt;

Also, there are blue and orange colours in your data. Should you be showing them too?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.