March 17, 2011 at 4:59 am
Hi sirs !
I have a Table like this (to simplify) :
COL1 | COL2 | COL3 | COL4 |... | COL24 | COL_MAX_COL
12 | 23 | 21 | 2 |... | 4 | (value to fill)
I need a query to recover the max value (in the sample is 23) and then NAME OF THE COLUMN (in the sample COL2), with the columnname, i would like to UPDATE that row in the COL_MAX_COL with the columnName returned.
Can somebody help me ??? I'm tired...seraching a lot in google and no results... I 've achieved to recover the max value (using a user defined function), but no method to recover the column...
Thanks in advance for your great support !!
Frank
March 17, 2011 at 6:30 am
Hi Frank
This is one possible solution using unpivot
CREATE TABLE DBO.SOMETABLE
(
ROWID INT IDENTITY(1,1),
COL1 INT,
COL2 INT,
COL3 INT,
COL4 INT,
COL5 INT
)
INSERT INTO DBO.SOMETABLE(COL1,COL2,COL3,COL4,COL5) VALUES(1,6,3,8,9)
INSERT INTO DBO.SOMETABLE(COL1,COL2,COL3,COL4,COL5) VALUES(23,1,6,4,3)
SELECT COL1,COL2,COL3,COL4,COL5,M.COL_MAX_COL
FROM DBO.SOMETABLE T
LEFT JOIN
(
SELECT ROWID,MAX(col) COL_MAX_COL FROM
(SELECT * FROM DBO.SOMETABLE) UNPIV
UNPIVOT
(Col FOR ColName IN ([COL1],[COL2],[COL3],[COL4],[COL5])) CHLD
GROUP BY ROWID
) M
ON M.ROWID = T.ROWID
DROP TABLE SOMETABLE;
This query can handle multiple rows if you need them. if not you can just remove the RowId references
March 17, 2011 at 7:16 am
Hi Frank
I`ve only half answered your question, this will return the column name as well
CREATE TABLE DBO.SOMETABLE
(
ROWID INT IDENTITY(1,1),
COL1 INT,
COL2 INT,
COL3 INT,
COL4 INT,
COL5 INT
);
INSERT INTO DBO.SOMETABLE(COL1,COL2,COL3,COL4,COL5) VALUES(1,6,3,8,9);
INSERT INTO DBO.SOMETABLE(COL1,COL2,COL3,COL4,COL5) VALUES(24,23,6,4,3);
INSERT INTO DBO.SOMETABLE(COL1,COL2,COL3,COL4,COL5) VALUES(24,29,6,29,3);
with UnpivotedCol(Rowid,ColValue,ColName) as
(
SELECT ROWID,ColValue,ColName FROM
(SELECT * FROM DBO.SOMETABLE) UNPIV
UNPIVOT
(ColValue FOR ColName IN ([COL1],[COL2],[COL3],[COL4],[COL5])) CHLD
)
select sometable.*,ColValue [MAX_COL_VALUE],ColName [MAX_COL_NAME]
from DBO.SOMETABLE someTable
left join
(
select base.RowId,base.ColValue,min(base.ColName) ColName from UnpivotedCol base
inner join (select RowId,MAX(colvalue) colValue from UnpivotedCol group by RowId) maxValues
on base.Rowid = maxValues.rowId and base.ColValue = maxValues.colValue
group by base.RowId,base.ColValue
) t on t.Rowid = someTable.ROWID;
DROP TABLE SOMETABLE;
I have made an assumption here, if there are multiple columns with the same max value then i will return the first column name(Alphabetically)
March 26, 2011 at 3:41 am
Thank you ! Sir... I ll try soon... I haved a busy days and i could'nt try it... I ve seen the answer today... 10 points for you ! Thanks again !
Frank
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply