Results 1 to 2 of 2

Thread: Highest Amount

  1. #1
    Join Date
    Sep 2002
    Location
    NJ
    Posts
    139

    Unanswered: Highest Amount

    HI:

    I HAVE DESIGNED THREE TABLES IN ORACLE8I INSTALLED IN WIN2K

    THE TOWN TABLE
    TOWN_ID,
    TOWN_NAME

    THE BUIDING TABLE
    BUILDING_ID
    BUILDING_NAME

    THE TENANT TABLE
    TENANT_AMOUNT
    BUILDING_ID FK REF BUILDING ID IN BUILDING
    TOWN_ID FK REF TOWN ID IN TOWN

    I WANT TO FIND OUT WHAT THE HIGHEST AMOUNT IS IN TENANT TABLE AS IT RELATES TO THE TOWN OF NEW YORK, IN BULDING TRUMP PLAZA.

    I HAVE WRITTEN THIS QUERY BUT IT RETURNS ALL THE ROWS. HOW CAN I DO IT?

    HERE IS WHAT I HAVE TRIED:

    SELECT T.TOWN_NAME, R.TENANT_AMOUNT, P.BUILDING_NAME
    FROM TENANT R, BUILDING P, TOWN T
    WHERE P.BUILDING_NAME = 'NEW YORK'
    AND R.TOWN_ID = T.TOWN_ID
    AND R.BUILDING_ID = P.BUILDING_ID;


    TOWN_NAME TENANT_AMO BUILDING_NAME
    -------------- ---------- ---------------
    New Yor 8000 Bldg d2
    Albany 1607 Bldg d3
    >>>> and on and on...

    BUT THAT GIVES ME ALL THE ROWS ASSOCIATED WITH THE TOWN NAME

    I TRIED THIS AS WELL:

    SELECT T.TOWN_NAME, R.TENANT_AMOUNT, P.BUILDING_NAME
    FROM TENANT R, BUILDING P, TOWN T
    WHERE P.BUILDING_NAME = 'NEW YORK'
    AND R.TOWN_ID = T.TOWN_ID
    AND R.BUILDING_ID = P.BUILDING_ID
    AND R.TENANT_AMOUNT = (SELECT MAX(TENANT_AMOUNT) FROM TENANT);

    I WANT THE RESULT TO BE:

    TOWN TENAOUT_AMOUNT BUILDING
    --------------------------------------------------------
    NEW YORK 80000 Bldg d2

  2. #2
    Join Date
    Nov 2002
    Posts
    833
    try this

    SELECT T.TOWN_NAME, max(R.TENANT_AMOUNT), P.BUILDING_NAME
    FROM TENANT R, BUILDING P, TOWN T
    WHERE P.BUILDING_NAME = 'NEW YORK'
    AND R.TOWN_ID = T.TOWN_ID
    AND R.BUILDING_ID = P.BUILDING_ID;

Posting Permissions

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