September 26, 2012 at 8:12 am
i have a below scenario i need to select the max from valu1 value2 valu3 value4 when their corresponding flag is 1. suggest please
DECLARE @tab TABLE
(
flag1 BIT
,value1 INT
,flag2 BIT
,value2 INT
,flag3 BIT
,value3 INT
,flag4 BIT
,value4 INT
)
INSERT INTO @tab
VALUES ('1','35','1','45','0','35','3','65')
,('0','85','1','45','0','35','3','55')
SELECT * FROM @tab
result
65
55
Note: please see the result set ..i want max value of the record not for columns
September 26, 2012 at 8:26 am
select
Max_Value =
(
select max(a.x)
from
(
select x = t.value1 where t.flag1 = 1union all
select x = t.value2 where t.flag2 = 1union all
select x = t.value3 where t.flag3 = 1union all
select x = t.value4 where t.flag4 = 1
) a ),
t.*
from
@tab t
Results:
Max_Value flag1 value1 flag2 value2 flag3 value3 flag4 value4
----------- ----- ----------- ----- ----------- ----- ----------- ----- -----------
65 1 35 1 45 0 35 1 65
55 0 85 1 45 0 35 1 55
(2 row(s) affected)
September 26, 2012 at 8:26 am
With some help from this article from Dwain, I came up with a solution:
An Alternative (Better?) Method to UNPIVOT (SQL Spackle)[/url]
WITH Original AS( SELECT ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) rn,
*
FROM @tab)
SELECT MAX( Value) value FROM Original
CROSS APPLY( VALUES( 'Value1', value1, flag1),
( 'Value2', value2, flag2),
( 'Value3', value3, flag3),
( 'Value4', value4, flag4)) x ( Name, Value, flag)
WHERE flag = 1
GROUP BY rn
September 26, 2012 at 8:31 am
How about this?
WITH CTE AS
(
SELECT rownum, Value
FROM
(
SELECT rownum = row_number() over (order by (select null)), *
FROM @tab
) x
CROSS APPLY (values (case when flag1=1 then value1 else 0 end),
(case when flag2=1 then value2 else 0 end),
(case when flag3=1 then value3 else 0 end),
(case when flag4=1 then value4 else 0 end)) P (value)
)
SELECT Value = MAX(Value)
FROM CTE
GROUP BY rownum;
September 26, 2012 at 8:44 am
It seems that I like the other solutions better than mine.
Laurie, you should drop the else from your cases to allow negative numbers.
Mine won't work when all the flags are 0 (zero).
September 26, 2012 at 8:50 am
Luis Cazares (9/26/2012)
Laurie, you should drop the else from your cases to allow negative numbers.
Good idea - I've dropped a bit of excess code too, based on the other suggestions...
DECLARE @tab TABLE
(
flag1 BIT
,value1 INT
,flag2 BIT
,value2 INT
,flag3 BIT
,value3 INT
,flag4 BIT
,value4 INT
);
INSERT INTO @tab
VALUES ('1','35','1','45','0','35','3','65')
,('0','85','1','45','0','35','3','55')
,('0','85','1','-45','0','35','3','-55');
--SELECT * FROM @tab;
SELECT Value=max(value)
FROM
(
SELECT rownum = row_number() over (order by (select null)), *
FROM @tab
) x
CROSS APPLY (values (case when flag1=1 then value1 end),
(case when flag2=1 then value2 end),
(case when flag3=1 then value3 end),
(case when flag4=1 then value4 end)) P (value)
GROUP BY rownum;
September 26, 2012 at 9:36 am
actually these values are date in format yyyymmdd
September 26, 2012 at 9:48 am
AAAGH.. Well it'll still work 😀
September 26, 2012 at 9:53 am
thank you Laurie.
September 26, 2012 at 10:01 am
You're welcome
September 26, 2012 at 10:03 am
laurie.. a small modification. the flags are not bit type anymore but int..i need to do same logic but flag are not null/empty and pull back the value of flag as well
DECLARE @tab TABLE
(
flag1 INT
,value1 INT
,flag2 INT
,value2 INT
,flag3 INT
,value3 INT
,flag4 INT
,value4 INT
);
INSERT INTO @tab
VALUES ('1','35','2','45','','35','4','65')
,('','85','5','45','8','35','4','55')
result
4, 65
4, 55
please suggest
September 26, 2012 at 10:29 am
That's a bit more complicated:
I've used CTEs because I want to refer to CTE1 twice.
The ints do not need quotes in their definition, so I've removed them.
DECLARE @tab TABLE
(
flag1 INT
,value1 INT
,flag2 INT
,value2 INT
,flag3 INT
,value3 INT
,flag4 INT
,value4 INT
);
INSERT INTO @tab
VALUES (1,35,2,45,null,35,4,65)
,(null,85,5,45,8,35,4,55)
,(null,85,7,50,3,35,1,25); -- extra row so not all 4s
/*
result
4, 65
4, 55
*/
WITH CTE1 AS
(
SELECT RowNum = row_number() over (order by (select null)), *
FROM @tab
),
CTE2 AS
(
SELECT RowNum, Value=max(value)
FROM CTE1
CROSS APPLY (values (case when flag1<>0 then value1 end),
(case when flag2<>0 then value2 end),
(case when flag3<>0 then value3 end),
(case when flag4<>0 then value4 end)) P (value)
GROUP BY RowNum
)
SELECT
Flag=case when value=value1 then flag1
when value=value2 then flag2
when value=value3 then flag3
when value=value4 then flag4 end,
CTE2.Value
FROM CTE2
INNER JOIN CTE1
ON CTE1.RowNum=CTE2.RowNum;
September 26, 2012 at 10:58 am
Thank you soo much .. u rock
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply