Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Oct 2006
    Posts
    14

    Unanswered: How do i select one row

    lets say we have this table: tbltel_Number Columns are Id_Number,Contract_No, Tel_Number and Tel_Type
    So lets say one ID_Number can have one Contract_Number but it can have different Tel_numbers and Different Tel_Type i.e Home_Number,Cell_Number and Employer_Number etc. So my question is if we have a one Tel_Number with different Tel_Type how do you select one row if everything is the same except the Tel_Type.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    well, i see that you are using Id_Number, so i'm going to assume it is one of those ubiquitous identity columns

    so, give a value for that column and you will get a row
    Code:
    select Id_Number
         , Contract_No
         , Tel_Number 
         , Tel_Type
      from tbltel_Number
     where Id_Number = 937
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2006
    Posts
    14

    how to select a row

    Okay, what meant was: how do I select one row if the everything is identical except the Tel_Type. This is the table:

    ID_Number Contract_No Tel_No Tel_Type
    1234 3221 555-464 Home_Number
    1234 3221 555-464 Employer_Number

    Now, how do I select one of the rows, because if u notice,everything is the same except for the type. And another the table has more than 50000 rows,so i need where i won't have to specify the actual values in the colums

  4. #4
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Think about the distinct keyword and how it might apply here. If in doubt, look it up in BOL. Apply said learned knowledge to a test table with a small amount of test data, and you will have your answer.

    -- This is all just a Figment of my Imagination --

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Sany84
    Okay, what meant was: how do I select one row if the everything is identical except the Tel_Type.
    okay, then i will ask you, "which row do you want?"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2006
    Posts
    14

    how do i select a row

    I need to select one of the two rows if the Telephone Numbers are the same.I've tried using distinct but it still does not work.

  7. #7
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Quote Originally Posted by Sany84
    I need to select one of the two rows if the Telephone Numbers are the same.I've tried using distinct but it still does not work.
    Try this...
    Code:
    select distinct Tel_Number ---corrected 
         , Id_Number
         , Contract_No
         , Tel_Type
      from tbltel_Number
    FYI,you will get results when Id_number,Contact_No,Tel_type are same in two rows.
    If they are different distinct will not work...
    Last edited by rudra; 11-03-06 at 05:01.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    rudra, i'm not sure if you knew this, since so many of your other posts have been so helpful...

    DISTINCT is not a function -- what you just wrote surely gives that impression, and it's wrong

    Sany84, try this --
    Code:
    select ID_Number 
         , Contract_No 
         , Tel_No 
         , Tel_Type
      from tbltel_Number as T
     where Id_Number = 937 
       and Tel_Type =
           ( select min(Tel_Type)
               from tbltel_Number
              where Id_Number = T.Id_Number
                and Contract_No = T.Contract_no
                and Tel_No = T.Tel_No )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    My MISTAKE... It was a silly one ,I know DISTINCT is a keyword not a function...Thanks for correcting me Rudy,I should be more cautious in future..
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  10. #10
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    Hey Rudy,
    I have used your code but I am not getting the required results ,
    may be I am doing some mistake...plz comment
    Code:
    create table x (
    title varchar(10)
    ,contract varchar(10)
    ,code varchar(10)
    ,type varchar(10))
    
    insert into x
    select 'a','a1','123','ter' UNION ALL
    select 'a','a1','127','prr' UNION ALL
    select 'a','a01','123','ter' UNION ALL
    select 'b','a21','888','ter' UNION ALL
    select 'b','a31','888','dee' UNION ALL
    select 'c','a11','167','ter' UNION ALL
    select 'd','a12','100','ter' UNION ALL
    select 'd','a13','145','cdd' UNION ALL
    select 'd','a13','145','ter' UNION ALL
    select 'd','a14','145','ter' UNION ALL
    select 'e','a15','111','ter' UNION ALL
    select 'f','a16','122','ter' 
    
    select  a.title
             ,a.type
             ,a.contract
             ,a.code 
    from x a
    where a.title='a' 
    and  a.type=(select min(b.type) from x b
                        where a.title=b.title
                         and a.contract=b.contract
                           and a.code=b.code)
    
    
    drop table x
    Code:
    ---Current results---
    Title     Type      Contact    Code
    a	prr	a1	127
    a	ter	a01	123
    a	ter	a1	123
    
    ---Required results---
    Title     Type      Contact    Code
    a	prr	a1	127
    Last edited by rudra; 11-03-06 at 05:11.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  11. #11
    Join Date
    Oct 2006
    Posts
    14
    Thanks r937 this works but you have to put distinct in your first select statement and the reason i took out the id_Number = 937 in the where clause it's bcoz we are select for a bulk ID's see below:

    select Distinct ID_Number
    , Contract_No
    , Tel_No
    , Tel_Type
    from tbltel_Number as T
    where Tel_Type =
    ( select min(Tel_Type)
    from tbltel_Number
    where Id_Number = T.Id_Number
    and Contract_No = T.Contract_no
    and Tel_No = T.Tel_No )

  12. #12
    Join Date
    Nov 2006
    Posts
    11

    How do i select a row

    select ID_Number
    , Contract_No
    , Tel_Number
    , Telephone_Type_Name
    from tbltel_Number as T
    where ID_Number = 937 and
    Telephone_Type_Name =
    ( select min(Telephone_Type_Name)
    from tbltel_Number
    where Id_Number = T.Id_Number
    and Contract_No = T.Contract_no
    and Tel_Number = T.Tel_Number )

    I'm not getting the required results, i still get the same tel_number with a different telephone_type_name.

  13. #13
    Join Date
    Nov 2006
    Posts
    11
    Current results:
    ID_Number Contract_No Tel_Number Telephone_Type_Name
    12345 255 555-33 Home Number
    12345 255 555-33 Bussines Number


    Required Results:
    ID_Number Contract_No Tel_Number Telephone_Type_Name
    12345 255 555-33 Home Number

  14. #14
    Join Date
    Nov 2005
    Location
    San Francisco, CA
    Posts
    506
    I am posting this for no more ambiguity...The code is ok ..bcoz

    The poster asked for this ---
    Code:
    So my question is if we have a one Tel_Number with different Tel_Type 
    how do you select one row if everything is the same except the Tel_Type.
    so the data should be in this pattern,i.e. all columns are same except type

    Code:
    insert into x
    select 'a','a1','123','0' UNION ALL
    select 'a','a1','123','1' UNION ALL
    select 'a','a1','123','2' UNION ALL
    select 'b','a21','888','1' UNION ALL
    select 'b','a21','888','2' UNION ALL
    select 'c','a11','167','1' UNION ALL
    select 'd','a12','145','1' UNION ALL
    select 'd','a12','145','2' UNION ALL
    select 'd','a12','145','3' UNION ALL
    select 'd','a12','145','4' UNION ALL
    select 'e','a15','114','1' UNION ALL
    select 'f','a16','111','1'
    check this code
    Code:
    create table x (
    title varchar(10)
    ,contract varchar(10)
    ,code varchar(10)
    ,type varchar(10))
    
    insert into x
    select 'a','a1','123','0' UNION ALL
    select 'a','a1','123','1' UNION ALL
    select 'a','a1','123','2' UNION ALL
    select 'b','a21','888','1' UNION ALL
    select 'b','a21','888','2' UNION ALL
    select 'c','a11','167','1' UNION ALL
    select 'd','a12','145','1' UNION ALL
    select 'd','a12','145','2' UNION ALL
    select 'd','a12','145','3' UNION ALL
    select 'd','a12','145','4' UNION ALL
    select 'e','a15','111','1' UNION ALL
    select 'f','a16','114','1' 
    
    select distinct a.title
             ,a.type
             ,a.contract
             ,a.code 
    from x a
    where 
    --a.title='a' 
    --and  
    a.type=(select min(b.type) from x b
                        where a.title=b.title
                         and a.contract=b.contract
                           and a.code=b.code)
    
    
    drop table x
    Using distinct in Rudy's code the correct results are displayed..
    plz ignore my pms..
    Last edited by rudra; 11-03-06 at 05:30.
    Success is the ability to go from one failure to another with no loss of enthusiasm.
    - Sir Winston Churchill
    Joydeep

  15. #15
    Join Date
    Nov 2006
    Posts
    11
    if you have about 100 000 rows in your table this is not working trust me.

Posting Permissions

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