how to get max column name that meets certain criteria

Question

Post reply

how to get max column name that meets certain criteria

sql84

SSC Eights!

Points: 830
More actions
February 10, 2014 at 10:02 am

#303957

here is a sample table:
Create table #TableA (ID INT
, [1] DECIMAL(6,5)
,[1.1] DECIMAL(6,5)
, [2] DECIMAL(6,5)
,[2.2] DECIMAL(6,5)
, [3] DECIMAL(6,5)
,[3.3] DECIMAL(6,5)
, [4] DECIMAL(6,5)
,[4.4] DECIMAL(6,5)
, FLAGB1 CHAR(1)
, FLAGB2 float)
INSERT INTO #TABLEA (ID, [1],[1.1],[2],[2.2],[3],[3.3],[4],[4.4],FLAGB1,FLAGB2 )
VALUES(1, 1.1111,null, 2.2222,0.1234, 3.3333,null,null, 1.4444,'y',null )
, (2, null,1.2345, 2.2345,null, 3.2345, 4.2345,1.5437,0.2378,null,null)
,(3, 5.3789,1.2222,0.23864,null, 2.2222, 3.2222, 4.2222,null,'y',null)
I need column FLAGB2 to get the MAX[column-name] if FLAGB1 is 'Y' and MAX[column-name] should not be 'null'. apologies, i couldn't put the requirement in a sentence(s) more clearly, so the result should look like this:
[ID]-------[1]-----------[1.1]---------[2]-----------[2.2]---------[3]-----------[3.3]---------[4]----------[4.4]--------[FLAGB1]-------[FLAGB2]
1---------1.11110------NULL--------2.22220-----0.12340------3.33330-----NULL--------NULL--------1.44440--------y--------------4.4
2---------NULL---------1.23450-----2.23450-----NULL---------3.23450-----4.23450-----1.54370-----0.23780-------NULL--------- --NULL
3---------5.37890------1.22220-----0.23864-----NULL---------2.22220-----3.22220-----4.22220-----NULL-----------y----------------4
note: column values has nothing to do wth populating FLAGB2, values can be anything except null but the colum,n itself needs to be the highest than other columns(not null)

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

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

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

This seems like a weird design decision.

You could use something like this.

UPDATE A SET

FLAGB2 = CASE

WHEN [4.4] IS NOT NULL THEN 4.4

WHEN [4] IS NOT NULL THEN 4

WHEN [3.3] IS NOT NULL THEN 3.3

WHEN [3] IS NOT NULL THEN 3

WHEN [2.2] IS NOT NULL THEN 2.2

WHEN [2] IS NOT NULL THEN 2

WHEN [1.1] IS NOT NULL THEN 1.1

WHEN [1] IS NOT NULL THEN 1 END

FROM #TableA A

WHERE FLAGB1 = 'y'

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

Completeley agree with Luis: very weird design decision...

However there is another puzzle solution:

UPDATE #TableA

SET FLAGB2 = COALESCE(4.4 * [4.4]/[4.4],4 * [4]/[4],3.3 * [3.3]/[3.3],3 * [3]/[3],2.2 * [2.2]/[2.2],2 * [2]/[2],1.1 * [1.1]/[1.1],1 * [1]/[1])

WHERE FLAGB1 = 'Y'

SELECT * FROM #TableA

will not work if at least one column contains zero 0.

In this case the division bit should be changed to: [COL]/NULLIF([COL],0)

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

sql84 SSC Eights! Points: 830 More actions · Answer 3

thank you both for your input. i agree with ya'll, it is a weird table design. thing is this a derived table from 6 different tables. we could have done it better but well... anyways my group decided to change the requirement by adding few more columns and we solved the problem. i tried the queries ya'll posted it works for what i asked. 🙂