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 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy