April 20, 2011 at 6:19 pm
how to compare four columns and select only one column value out of four
ex: col 1 - apple
col 2 - boy
col3 - null
col4 - cat
i want to compare columns and return cat(descending alphabetical order)
lp
I can use coalsce but it is returning value boy(first non null) instead of cat
pls he
April 20, 2011 at 6:29 pm
You'll have to unpivot the data, sort it desc, then select top 1 for each item.
If you can provide a sample DDL/Data set like you'll find described in the first link in my signature, we can walk you through it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 20, 2011 at 6:36 pm
thank you for the reply
i am using it in a dynamic sql so unpivot is difficult?
Is there any other way like using coalsce and order by ?
pls suggest
April 20, 2011 at 7:01 pm
How about:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[ufnlastcolumn](
@col1 varchar(255), @col2 varchar(255), @col3 varchar(255), @col4 varchar(255)
)
RETURNS varchar(255)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @ReturnValue varchar(255);
set @ReturnValue = 'a';
If @col1 > @ReturnValue
set @ReturnValue = @col1;
If @col2 > @ReturnValue
set @ReturnValue = @col2;
If @col3 > @ReturnValue
set @ReturnValue = @col3;
If @col4 > @ReturnValue
set @ReturnValue = @col4;
RETURN (@ReturnValue);
END;
GO
select dbo.ufnlastcolumn('zapple', 'boy',NULL,'dat')
April 20, 2011 at 7:54 pm
thanks all for ur help
i think i found the solution using the above( i am not using function to include in dynamic sql)
i used coalesce and >. Let me know if this is not correct
data : col1 - apple,col2 - zebra, col3 - cat, col4 - null
select (case when((coalesce(col1,'a')> coalesce(col2,'a')) and (coalesce(col1,'a')> coalesce(col3,'a')) and (coalesce(col1,'a')> coalesce(col4,'a'))) then col1
when((coalesce(col2,'a')> coalesce(col1,'a')) and (coalesce(col2,'a')> coalesce(col3,'a')) and (coalesce(col2,'a')> coalesce(col4,'a'))) then col2
when((coalesce(col3,'a')> coalesce(col1,'a')) and (coalesce(col3,'a')> coalesce(col2,'a')) and (coalesce(col3,'a')> coalesce(col4,'a'))) then col3
when((coalesce(col4,'a')> coalesce(col1,'a')) and (coalesce(col4,'a')> coalesce(col2,'a')) and (coalesce(col4,'a')> coalesce(col3,'a'))) then col4
end ) as tt from testcol
April 21, 2011 at 12:08 am
How about this?
declare @table table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10)
)
insert @table values ( 'apple','boy',null, 'cat')
; with cte as
(
select * ,
RN = ROW_NUMBER() over( order by Val DESC)
from
( select col1, col2 , col3, col4 from @table ) unpivot_source_Table
unpivot
( ColNames for Val in (col1, col2 , col3, col4 )) unpivot_handle
)
select * from cte
where RN = 1
April 21, 2011 at 1:55 am
I was mulling over this problem and i found a bug with my code; So i came back running to fix it up :w00t:
So here is the hopefully successful (:-P) solution
declare @table table
(
col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10)
)
insert @table values ( 'apple','boy',null, 'cat')
insert @table values ( 'apple','zebra',null, 'cat')
select col1, col2 , col3, col4 from @table
; with cte as
(
select * ,
RN = ROW_NUMBER() over( partition by GrpNum order by Val DESC)
from
( select col1, col2 , col3, col4
, GrpNum = ROW_NUMBER() over( order by (select 0))
from @table ) unpivot_source_Table
unpivot
( Val for ColNames in (col1, col2 , col3, col4 )) unpivot_handle
)
select * from cte
where RN = 1
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply