Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004

    Question Unanswered: Distinct problem

    I have a table equip(name,failnumber)

    Now the name of the equipment can can appear more than once however the failnumber is unique.

    The failnumber is a foreign key to a second table i wish to join with so i can also get a column from the second table.

    What i need is to find a distinct name from equip and failnumber.

    I have tried

    select distinct eqnum, failnumber from equip

    but this obviously returns all the eqnums and all the failnumber records.

    Any clues how i can only get one distinct eqnum and a corresponding failnumber?


  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1

    Re: Distinct problem

    select eqnum, min(failnumber) from equip group by eqnum;

  3. #3
    Join Date
    Apr 2004
    Ok thats cool but lets say i have the tables below


    name Failnumber

    Car 1000
    Car 1001
    Car 1002
    Truck 1003
    Truck 1004
    Truck 1005

    Other table

    Failnumber Vendor
    1000 X Company
    1001 X Company
    1002 X Company
    1003 Y Company
    1004 Y Company
    1005 Y Company

    I am looking for this sought of result

    equipname vendor
    car X Company
    Truck Y Company

    So I basically need a distinct and then join it to the table below and get the accompaning Vendor.

    If I try

    select eqnum,min(failnumber),other.vendor from equip,other where equip.failnumber in(

    select eqnum, min(failnum) from equip group by eqnum)

    I get shot down with Ora-00913P: Too many values

  4. #4
    Join Date
    Sep 2002
    Provided Answers: 1
    Looks like you want:

    select distinct, other.vendor
    from equip, other
    where equip.failnumber = other.failnumber;

Posting Permissions

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