March 30, 2010 at 4:00 am
I have a table with 5 count colums (A, B, C, D, E)
I was wondering if there was a way to create a function that will create a computed field (called "RANK") that ranks these 5 count columns and sets a priority level based on the following criteria:
Column Priority
Column C 1
Column B 2
Column E 3
Column D 4
Column A 5
The idea is this. For each row I want to know which of the five columns has the highest count value and have the "RANK" column populated with the name of the field with the highest count value for that record. The priority settings are there incase two fields have the same count value, but populate the RANK column with the field name that has the highest priority.
I thought of case statements to code for every possible combination, but wonder if there is a more efficient way of doing ths.
Any help would be much appreciated.
March 30, 2010 at 4:26 am
Sorry, I removed my reply, since I misunderstood your question.
-- Gianluca Sartori
March 30, 2010 at 4:33 am
What I need is a single computed field to tell me which of the 5 count columns has the highest value for that row
March 30, 2010 at 4:38 am
hi mark, can u pls post some visual examples of your table which has the count and your expected result.. i actually semi-understood ur question :(.. any help in this wud be helpful for us
March 30, 2010 at 4:46 am
Hi, here is a sample and expected result
A B C D E Expected result
2 2 140 2 2 C (column C has the highest value)
2 2 88 2 2 C (column C has the highest value)
1 2 88 2 2 C (column C has the highest value)
2 2 2 2 2 C (based on the priority criteria C is displayed)
Hope this helps. Expected result is the computed field. Sorry for the messy output, can't seem to space it out better.
March 30, 2010 at 5:09 am
it's been a while since i had to do this;
youy can do it with a case statement, comparing multiple value pairs:
select
[Column A],
[Column B],
[Column C],
[Column D],
[Column E],
case
when [Column C] >=[Column A]
and [Column C] >=[Column B]
and [Column C] >=[Column D]
and [Column C] >=[Column E]
then 'C (column C has the highest value)'
when [Column B] >=[Column A]
and [Column B] >=[Column D]
and [Column B] >=[Column E]
then 'B (column B has the highest value)'
when [Column E] >=[Column A]
and [Column E] >=[Column D]
then 'E (column E has the highest value)'
when [Column D] >=[Column A]
then 'D (column D has the highest value)'
else 'A (column C has the highest value)'
end As DesiredResults
from yourtable
Lowell
March 30, 2010 at 5:13 am
Thanks Lowell. But I wanted to include the priority settings as well, but can't figure out how to incorporate it.
March 30, 2010 at 5:19 am
the case statement i posted had the priority you stated, so do you mean the "priority" may be dynamic and passed as a parameter or something?
Lowell
March 30, 2010 at 5:28 am
If you look at the column priorties, Column E has a higher priority than Column A. There when both Columns E and A are = 1 then I would expect Column E to be return. Here is an example of what is returned:
A B C D E Result
1 0 0 0 1 A
I would expect:
A B C D E Result
1 0 0 0 1 E
regards
March 30, 2010 at 5:48 am
HI mark, here is your function
Sample table and data
if object_id('tempdb..#test') is not null
drop table #test
create table #test
(
A smallint,
B smallint,
C smallint,
D smallint,
E smallint
)
INSERT INTO #test
SELECT 1,2,3,5,4
UNION ALL
SELECT 1,2,3,5,4
UNION ALL
SELECT 100,2,3,5,4
UNION ALL
SELECT 1,2,3,5,5
if object_id('priority_look_up') is not null
drop table priority_look_up
create table priority_look_up
(
col_nam varchar(10),
Rank int
)
insert into priority_look_up
select 'A', 1
union all
select 'B', 2
union all
select 'C', 3
union all
select 'D', 4
union all
select 'E', 5
select * From #test
GO
And here goes your function
;IF OBJECT_ID('dbo.fn_getPriorityCol') IS NOT NULL
AND OBJECTPROPERTY(OBJECT_ID('dbo.fn_getPriorityCol'), 'IsScalarFunction') = 1
BEGIN
DROP FUNCTION dbo.fn_getPriorityCol
END
CREATE FUNCTION dbo.fn_getPriorityCol (@c1 int, @c2 int, @c3 int, @c4 int, @c5 int)
RETURNS VARCHAR(2)
AS
BEGIN
DECLARE @Pri_Col VARCHAR(2)
declare @temp table (col_nam varchar(64), val int)
insert into @temp
select 'A', @c1
union all
select 'B', @c2
union all
select 'C', @c3
union all
select 'D', @c4
union all
select 'E', @c5
IF ( select COUNT(*) from @temp where val = (select max(val) from @temp) ) = 1
BEGIN
select @Pri_Col = col_nam from @temp where val = (select max(val) from @temp)
END
ELSE
BEGIN
select @Pri_Col = col_nam from priority_look_up where rank =
(select max(rank) from @temp t
join priority_look_up p
on t.col_nam = p.col_nam
where val = (select max(val) from @temp) )
END
RETURN @Pri_Col
ENd
--Test the function
select dbo.fn_getPriorityCol (A,B, C, D, E) from #test
Please inform us if this is what u expected...
I had not made a computed column out of the function, it is upto u to create it 🙂
Cheers,
C'est Pras!
Edit : Fixed an input variable
March 30, 2010 at 6:01 am
That function probably is not the best-perfomant, rather a clean-working one..
here is a table where i used taht function as computed column, it worked fine for me
create table testings
(
A smallint,
B smallint,
C smallint,
D smallint,
E smallint,
Expected AS dbo.fn_getPriorityCol(A,B, C, D, E)
)
INSERT INTO testings
SELECT 1,2,3,5,4
UNION ALL
SELECT 1,2,3,5,4
UNION ALL
SELECT 100,2,3,5,4
UNION ALL
SELECT 1,2,3,5,5
select * from testings
Please let me know if i had given u a correct solution..;-)
Cheers!
March 30, 2010 at 6:02 am
mark you did not try my case statement; it does produces the desired results; maybe with the switch from pseudo/sample to your real code something got lost;
here's a full example, with all the desired results you asked for:
Column A Column B Column C Column D Column E DesiredResults
----------- ----------- ----------- ----------- ----------- ----------------------------------
2 2 140 2 2 C (column C has the highest value)
2 2 88 2 2 C (column C has the highest value)
1 2 88 2 2 C (column C has the highest value)
2 2 2 2 2 C (column C has the highest value)
1 0 0 0 1 E (column E has the highest value)
create table #sample(
[Column A] int,
[Column B] int,
[Column C] int,
[Column D] int,
[Column E] int)
insert into #sample
SELECT 2,2,140,2,2 UNION ALL
SELECT 2,2,88,2,2 UNION ALL
SELECT 1,2,88,2,2 UNION ALL
SELECT 2,2,2,2,2 UNION ALL
SELECT 1,0,0,0,1
select
[Column A],
[Column B],
[Column C],
[Column D],
[Column E],
case
when [Column C] >=[Column A]
and [Column C] >=[Column B]
and [Column C] >=[Column D]
and [Column C] >=[Column E]
then 'C (column C has the highest value)'
when [Column B] >=[Column A]
and [Column B] >=[Column D]
and [Column B] >=[Column E]
then 'B (column B has the highest value)'
when [Column E] >=[Column A]
and [Column E] >=[Column D]
then 'E (column E has the highest value)'
when [Column D] >=[Column A]
then 'D (column D has the highest value)'
else 'A (column C has the highest value)'
end As DesiredResults
from #sample
Lowell
March 30, 2010 at 6:10 am
Lowell, ur code works fine.. but i think u missed one part in mark's question..
Mark says that if there is match between the values present in the columns, then he asks us to look at one priority table which will tel us which column must be the top priority in the matching columns and use that column accordingly..
Please look at the funtion i have given..
March 30, 2010 at 6:42 am
markjrouse (3/30/2010)
Hi, here is a sample and expected resultA B C D E Expected result
2 2 140 2 2 C (column C has the highest value)
2 2 88 2 2 C (column C has the highest value)
1 2 88 2 2 C (column C has the highest value)
2 2 2 2 2 C (based on the priority criteria C is displayed)
Hope this helps. Expected result is the computed field. Sorry for the messy output, can't seem to space it out better.
Mark,
My recommendation would be to read and heed the article located at the first link in my signature line below. If you make it easy for people to help you, a whole lot more people will. 😉
--Jeff Moden
March 30, 2010 at 6:58 am
Hi SSC-Enthusiastic,
it sort of works. This is what I get:
A B C D E result
2 2 2 2 2 A
2 2 2 2 2 A
1 2 2 2 2 D
1 8 0 1 1 B
1 2 2 2 2 D
1 8 0 1 1 B
This is what I expected:
A B C D E Expected
2 2 2 2 2 C (as it has the highest priority)
2 2 2 2 2 C (as it has the highest priority)
1 2 2 2 2 C (as it has the highest priority)
1 8 0 1 1 B correct
1 2 2 2 2 C (as it has the highest priority)
1 8 0 1 1 B correct
it returns the correct result when it is clear what column is the highest, but when the columns have the same "2" value it returns the wrong Column name. I changed the priority look up table as:
insert into dbo.priority_look_up
select 'C', 1
union all
select 'B', 2
union all
select 'E', 3
union all
select 'D', 4
union all
select 'A', 5
regards
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply