Using MAX on a query created column

Question

Post reply

Using MAX on a query created column

Shabbaranks

SSC Eights!

Points: 880

December 14, 2020 at 9:59 am

#3820191

Hi,

I have created a query which narrows down my data but I would like then to max that column (Stage in my example) but I cant work out how to do it?

SELECT 
DISTINCT([Code]),
       COALESCE([QTY_1],0) As "Test 1",
       COALESCE([QTY_2],0) As "Test 2",
       COALESCE([QTY_3],0) As "Test 3",
       COALESCE([QTY_4],0) As "Test 4",
       COALESCE([QTY_5],0) As "Test 5",
       COALESCE([QTY_6],0) As "Test 6",
   CASE 
   WHEN [QTY_6] >=1 THEN '6'
   WHEN [QTY_5] >=1 THEN '5'
   WHEN [QTY_4] >=1 THEN '4'
   WHEN [QTY_3] >=1 THEN '3'
   WHEN [QTY_2] >=1 THEN '2'
   WHEN [QTY_1] >=1 THEN '1'
   ELSE '0' END as Stage,
(SELCT MAX([Stage]) as (v)
  FROM [Table1]

Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246873 More actions · Answer 1

Will the MAX column be the same for every row of data returned, or are you looking for some sort of grouping?

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Shabbaranks SSC Eights! Points: 880 More actions · Answer 2

Hey Phil

Thanks for coming back to me. Ive probably not explained myself properly and maybe grouping is the answer. The code column can contain multiple duplicate records each of these will have a stage column score 0 - 6 and what Im trying to get is - return the highest scoring code column based on the stage column (am I right in thinking this is an alias because I have created it?)

Have I confused the question even more?

Thanks

jcelko212 32090 SSCrazy Eights Points: 9302 More actions · Answer 3

>> Have I confused the question even more? <<

Yes. Did you read the part about posting DDL in these SQL forms? You really need a bASic course in data modeling; the postfix "_code" is what ISO calls an attribute property, it means I need an attribute in the definition. What kind of code is it? Postal? Customer? Something else? You also don't seem to believe that the table should have a key. You also numbered your quantity columns , which implies that this thing is not even in first normal form (look up "repeated group").

Since we have no sample data or anything. I'm going to make a guess that product code is something like a UPC or GTIN. I will further guess that these quantities are located somewhere in there were six warehouses or whatever. Furthermore, I'm presuming that each warehouse can have zero or more units of inventory.

CREATE TABLE Inventory

(product_code CHAR(10) NOT NULL,

warehouse_nbr INTEGER NOT NULL

CHECK(warehouse_nbr BETWEEN 1 AND 6),

PRIMARY KEY (product_code, warehouse_nbr),

product_qty INTEGER NOT NULL DEFAULT 0

CHECK (product_qty >= 0)

);

We can find which warehouse has the largest quantity. I'm am leaving the possibility that all the warehouses could be empty and have a quantity zero.

SELECT product_code, warehouse_nbr, product_qty,

MAX( product_qty) OVER(PARTITION BY product_code, warehouse_nbr) AS max_qty

FROM Inventory

WHERE product_qty = max_qty;

Now put this in another CTE to handle two warehouses with the same quantity however you wish.

Please post DDL and follow ANSI/ISO standards when asking for help.

Shabbaranks SSC Eights! Points: 880 More actions · Answer 4

Hi,

Thanks I managed to get this sorted by creating 2 queries and then joining them together. Where I was struggling was where I was placing the max element.

SELECT Code, max(
CASE
WHEN [QTY_6] >=1 THEN '6'
WHEN [QTY_5] >=1 THEN '5'
WHEN [QTY_4] >=1 THEN '4'
WHEN [QTY_3] >=1 THEN '3'
WHEN [QTY_2] >=1 THEN '2'
WHEN [QTY_1] >=1 THEN '1'
ELSE '0' END) as Stage   
FROM TABLE1 group by code