September 28, 2011 at 11:37 am
kishoremania (9/28/2011)
I am showing you dummy dataselect A,B,C from TEST
Output:
10 1 1
10 1 2
10 2 1
10 2 2
10 3 1
10 3 2
I need a query to get the record of (10,3,2) from table TEST.
Can anyone please help me out here.
Sorry to repost it(due to mistakes in last post).
I think this is what you are looking for:
select A, B, C from test where A = 10 and B = 3 and C = 2
September 28, 2011 at 11:39 am
Yes, you are right.
I need (10,3,2)
September 28, 2011 at 11:42 am
I mean to say that..
I need a query to get data of Max value in B and again in B, I need Max value in C
September 28, 2011 at 11:56 am
kishoremania (9/28/2011)
I mean to say that..I need a query to get data of Max value in B and again in B, I need Max value in C
So how about:
select a, b, c from test
where a = 10 and b = (select max(b) from test where a = 10)
and c = (select max(c) from test where a = 10 and b = (select max(b) from test where a = 10))
September 28, 2011 at 11:57 am
So, if I am understanding correctly, you don't want just
SELECT MAX(A), MAX(B), MAX(C) FROM test
You are looking for the max of A, and for all B values associated with MAX(A), you want MAX(B), and for all C values associated with MAX(B) of MAX(A), you want MAX(C)?
A query for that would look something like -
SELECT b.maxa, b.maxb, MAX(c.c)
FROM test c
INNER JOIN
(SELECT a.a as maxa, MAX(b.b) AS maxb
FROM test b
INNER JOIN ( SELECT MAX(A) as maxa FROM test) a
ON a.maxa = test.a
GROUP BY a.a) b
ON b.maxb = c.b
AND b.maxa = c.a
GROUP BY b.maxa, b.maxb
September 28, 2011 at 12:21 pm
You are right on B and C
but I need all A's where, for every A I need max(B) in that result again I need Max(C)
September 28, 2011 at 12:33 pm
Actually the data will be like
10 1 1
10 1 2
10 2 1
10 2 2
10 3 1
10 3 2
11 1 1
11 1 2
11 2 1
11 2 2
11 3 1
11 3 2
Output required is (10,3,2),(11,3,2)
September 28, 2011 at 12:40 pm
kishoremania (9/28/2011)
Actually the data will be like10 1 1
10 1 2
10 2 1
10 2 2
10 3 1
10 3 2
11 1 1
11 1 2
11 2 1
11 2 2
11 3 1
11 3 2
Output required is (10,3,2),(11,3,2)
This should work:
select a, b, c from test
where b = (select max(b) from test)
and c = (select max(c) from test where b = (select max(b) from test))
September 28, 2011 at 12:49 pm
Yes, this is working. This is what I want.
Truly....Thanks a lot.
September 28, 2011 at 1:09 pm
September 28, 2011 at 1:19 pm
How about this?
declare @test table ( a int, b int, c int )
insert into @test ( a, b , c)
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, 6, 4
union all select 11, 7, 5
select InnerTable.a , InnerTable.b , InnerTable.c
from
(
select a , b , c
,max_b = rank() over (partition by a order by b desc )
,max_c = row_number() over ( partition by a , b order by c desc )
from @test
) InnerTable
where InnerTable.max_b = 1 and InnerTable.max_c = 1
{Edit : Added extra sample rows to tests, per OP's new sample data}
September 28, 2011 at 1:20 pm
bkubicek (9/28/2011)
kishoremania (9/28/2011)
Actually the data will be like10 1 1
10 1 2
10 2 1
10 2 2
10 3 1
10 3 2
11 1 1
11 1 2
11 2 1
11 2 2
11 3 1
11 3 2
Output required is (10,3,2),(11,3,2)
This should work:
select a, b, c from test
where b = (select max(b) from test)
and c = (select max(c) from test where b = (select max(b) from test))
This works, but it is very costly. Scans the table 4 times 🙁 Plus this wont give the max of "b" and max 'c' in max of 'b' per each "a" ..
September 28, 2011 at 2:28 pm
Your solution is real sensible as I am using a table of millions of records, where I need to take care of Performance
September 28, 2011 at 2:32 pm
Can I know why you used rank() for max_b and row_number() for max_c?
September 28, 2011 at 2:38 pm
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply