Need a Query

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

  • 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

  • Yes, you are right.

    I need (10,3,2)

  • 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

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

  • 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

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

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

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

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

    Truly....Thanks a lot.

  • 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]

  • 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}

  • 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" ..

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

  • 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