September 28, 2011 at 11:35 am
I am showing you dummy data
select 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).
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-2 table ( a int, b int, c int )
insert into @test-2 ( 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-2
) 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?
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply