Results 1 to 3 of 3

Thread: SQL problem??

  1. #1
    Join Date
    Oct 2002
    Posts
    29

    Angry Unanswered: SQL problem??

    I have a table in sybase with following data.

    F_Name L_Name, Phone

    F1 L1 P1
    F2 L2 P1
    F3 L3 P2
    F4 L4 P4

    I want to write a query which selects distinct phone number and only one F_Name and L_Name .

    Output should be ,

    P1 F1 L1
    P2 F3 L3

    or

    P1 F2 L2
    P2 F3 L3.

    etc..

    I can write the query with cursor or using aggregate function as follows

    select phone, min(F_Name), min(L_Name) from table
    group by phone

    Is there any alternate way ?

    Thanks

  2. #2
    Join Date
    Sep 2002
    Posts
    8

    Re: SQL problem??

    Originally posted by nmr
    I have a table in sybase with following data.

    F_Name L_Name, Phone

    F1 L1 P1
    F2 L2 P1
    F3 L3 P2
    F4 L4 P4

    I want to write a query which selects distinct phone number and only one F_Name and L_Name .

    Output should be ,

    P1 F1 L1
    P2 F3 L3

    or

    P1 F2 L2
    P2 F3 L3.

    etc..

    I can write the query with cursor or using aggregate function as follows

    select phone, min(F_Name), min(L_Name) from table
    group by phone

    Is there any alternate way ?

    Thanks

  3. #3
    Join Date
    Sep 2002
    Posts
    8
    One more alterneative will be..

    Select Phone, F_Name, L_Name into #tbl1 from yourtbl where 1= 2

    Select distinct phone, , into #tbl1
    Update #tbl1
    Set F_Name = b. F_Name, L_Name =b. L_Name
    From #tbl1 a, yourtbl b
    Where a. phone = b. phone

    If this is the way the data is going to be used I would redesign the table..

Posting Permissions

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