January 6, 2011 at 9:10 pm
Hi
I am trying to select the row with the highest combination of values and cant figure out how to do it, hope some one can help.
Assume I have the following data
Key, Value1, Value2
1, 1, 3
2, 2, 2
3, 3, 1
How do I select the row that has the highest combination of value1 and value2, in this case it is the second row.
Thanks
Mike
January 6, 2011 at 9:24 pm
--you need to give the table stracture an data i did that for you
declare @mytable table(
[int] NULL,
[value1] [int] NULL,
[value2] [int] NULL
)
insert into @mytable
select 1,2,3
union all
select 1,3,3
union all
select 3,3,3
--than you select values
select top 1 *,(+value1+value2) as rowsum from @mytable
Order by rowsum desc
January 6, 2011 at 9:47 pm
please give a feedback
January 7, 2011 at 12:04 am
Thanks for your reply.
That works for that data set but not a more dispersed one, sorry my fault, should have been clearer.
If you use the following Dataset
1, 5
2, 4
5, 1
The row with the highest values is row 2, however if you add col2 and col3 all rows give the same value, where I would want to have row 2 returned.
January 7, 2011 at 12:10 am
if you need the highest combination
of val1 and val2
you need to write
select top 1 *,(value1+value2) as val_sum from @mytable
Order by rowsum desc
January 7, 2011 at 12:17 am
please give a feedback
January 7, 2011 at 12:29 am
Thanks but that wont always give me the row I am after because the sum of all 3 rows is 6.
Im not looking for the max value but rather the highest in each column.
Max value in col1 is 5 in row 3
Max value in col2 is 5 in row 3
Perhaps a better explination of what I am after is I am trying to find out what customer has the highest balance outstanding as well as the longest period outstanding.
Cust#, BalOutstanding, DaysOutstanding
1, $100, 10
2, $50, 50
3, $10, 100
The customer with the highest outstanding balance is Cust 1
The Customer with the highest number of days outstanding is Cust 3
Where Cust 2 is the one I am after
Hope that makes more sense
January 7, 2011 at 4:45 am
Hi,
My guess is that the product of the values is what you are after. I have modified the previous suggestion accordingly:
DECLARE @test-2 TABLE
(value1 INT,
value2 INT)
INSERT INTO @test-2(value1, value2)
select 100, 10 union all select 50, 50 union all select 10, 100
select top 1 *,(value1*value2) as val_res from @test-2
Order by val_res desc
You still need to handle the situation where the values are exactly same.
Cheers
January 7, 2011 at 11:52 am
That's an elegant solution, and as far as I can see it takes care of all possible combinations too.
Cheers
January 7, 2011 at 11:56 am
I am trying to find out what customer has the highest balance outstanding as well as the longest period outstanding.
Mike, respectfully, you need to work on stating your question more formallly. You are wasting people's time making them guess over and over again. The statement above can't be answered if one customer has a higher balance and another has a longer period outstanding. Someone is now guessing that they should calculate the production of the amount and the time period.
If you have some other formula that you are using please state it.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
January 7, 2011 at 7:20 pm
Thanks for your answers, they have been very helpful.
Cheers
Mike
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply