Need a Query

Question

Need a Query

kishoremania

Ten Centuries

Points: 1059
More actions
September 28, 2011 at 11:35 am

#243799

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).

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

bkubicek SSChampion Points: 10943 More actions · Answer 1

kishoremania (9/28/2011)
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).

I think this is what you are looking for:

select A, B, C from test where A = 10 and B = 3 and C = 2

kishoremania Ten Centuries Points: 1059 More actions · Answer 2

kishoremania

Ten Centuries

Points: 1059

September 28, 2011 at 11:39 am

#1387972

Yes, you are right.

I need (10,3,2)

kishoremania Ten Centuries Points: 1059 More actions · Answer 3

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

bkubicek SSChampion Points: 10943 More actions · Answer 4

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))

getoffmyfoot Hall of Fame Points: 3338 More actions · Answer 5

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

kishoremania Ten Centuries Points: 1059 More actions · Answer 6

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)

kishoremania Ten Centuries Points: 1059 More actions · Answer 7

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)

bkubicek SSChampion Points: 10943 More actions · Answer 8

kishoremania (9/28/2011)
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)

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))

kishoremania Ten Centuries Points: 1059 More actions · Answer 9

Yes, this is working. This is what I want.

Truly....Thanks a lot.

@SQLFRNDZ SSCrazy Eights Points: 9095 More actions · Answer 10

This is what you are looking for...

WITH Cte_test

AS

(select MAX(B) AS B, MAX(C) AS C FROM

test456 )

SELECT DISTINCT A,B.* FROM test456 A, Cte_test B

[font="Tahoma"]
--SQLFRNDZ[/url]
[/font]

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 11

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}

ColdCoffee SSC-Dedicated Points: 39971 More actions · Answer 12

bkubicek (9/28/2011)
kishoremania (9/28/2011)
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)
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" ..

kishoremania Ten Centuries Points: 1059 More actions · Answer 13

Your solution is real sensible as I am using a table of millions of records, where I need to take care of Performance

kishoremania Ten Centuries Points: 1059 More actions · Answer 14

Can I know why you used rank() for max_b and row_number() for max_c?