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

    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?

    Thanks

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

    Re: Distinct problem

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

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

    equip

    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 equip.name 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
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Looks like you want:

    select distinct equip.name, 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
  •