Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181

    Unanswered: tricky select statement

    Hi folks,
    This is probably fairly easy but I can't make it walk.

    I have two tables A (id,operator,description - primkey id) and
    B (id,msisdn,numb,oid - primkey id)

    there is a

    such that A contains values like
    1,11,descript1
    2,21,descript2
    3,31,descript3

    and B contains values like
    4,0868145098,61,1
    5,0868145098,62,2
    6,0868145098,63,1

    Now I want to select the MSISDN associated with the maximum value of numb (ie 63) from table B and associate it with the operator,description from table A where A.id=B.oid

    It should produce a single row as below

    B.msisdn A.operator A.description
    0868145098, 11, descript1

    Any help on this would be very much appreciated as I have no idea how to restrict it down to one row.
    Thanking you,
    Breen.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: tricky select statement

    This should do it:

    select B.msisdn A.operator A.description
    from a,b
    where a.id = b.oid
    and b.numb = (select max(numb) from b);

  3. #3
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi and thanks for your reply Tony,
    I should have gone a bit further in example content so ...

    such that A contains values like
    1,11,descript1
    2,21,descript2
    3,31,descript3

    and B contains values like
    4,0868145098,61,1
    5,0868145098,62,2
    6,0868145098,63,1
    8,0868145263,71,3
    9,0868145263,72,2

    It should produce:

    B.msisdn A.operator A.description
    0868145098, 11, descript1
    0868145263, 21, descript2

    I hope this makes a bit more sense.

    And thank you for any help,
    Breen.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    In that case:

    select B.msisdn A.operator A.description
    from a,b
    where a.id = b.oid
    and b.numb = (select max(b2.numb) from b b2 where b2.msisdn = b.msisdn);

  5. #5
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Thanking you very much Tony.
    You are a star,
    Breen.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •