September 28, 2011 at 2:38 pm
September 28, 2011 at 2:41 pm
kishoremania (9/28/2011)
Can I know why you used rank() for max_b and row_number() for max_c?
For every value in A, i am using RANK to calculate the highest B , so i will have to use RANK to get the rankings of B. when i put a Where rank() of b = 1, i will get the highest B.
As i know know the highest b, i just want to number the value in c for every combination of a and b, in descending order to get the the highest c. Now, you CAN use RANK instead of ROW_NUMBER() !!
Hope this helps..
September 29, 2011 at 3:35 am
Hi,
You can try this one.
DECLARE @test-2 TABLE
(A INT,B INT,C INT)
INSERT INTO @test-2
SELECT 10,1,1
UNION ALL
SELECT 10,1,2
UNION ALL
SELECT 10,2,1
UNION ALL
SELECT 10,2,2
UNION ALL
SELECT 10,3,1
UNION ALL
SELECT 10,3,2
UNION ALL
SELECT 11,1,1
UNION ALL
SELECT 11,1,2
UNION ALL
SELECT 11,2,1
UNION ALL
SELECT 11,2,2
UNION ALL
SELECT 11,3,1
UNION ALL
SELECT 11,3,2
SELECT MAX(A),MAX(B),MAX(C) FROM @test-2
GROUP BY A
Shatrughna
September 29, 2011 at 9:36 am
shatrughna (9/29/2011)
Hi,You can try this one.
DECLARE @test-2 TABLE
(A INT,B INT,C INT)
INSERT INTO @test-2
SELECT 10,1,1
UNION ALL
SELECT 10,1,2
UNION ALL
SELECT 10,2,1
UNION ALL
SELECT 10,2,2
UNION ALL
SELECT 10,3,1
UNION ALL
SELECT 10,3,2
UNION ALL
SELECT 11,1,1
UNION ALL
SELECT 11,1,2
UNION ALL
SELECT 11,2,1
UNION ALL
SELECT 11,2,2
UNION ALL
SELECT 11,3,1
UNION ALL
SELECT 11,3,2
SELECT MAX(A),MAX(B),MAX(C) FROM @test-2
GROUP BY A
Nope, wont work.. Try adding this row and see if it is what the OP wanted :
SELECT 10,1,100
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply