Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011
    Posts
    3

    Unanswered: Simple query issue.

    I have a database structure as ...

    Name Address Phone
    aa aaa 123
    bb ab 123
    ab abc 111
    as ae 123
    ab abc 111
    ar ab 111

    i have to find all the records that has a unique phoneno.
    desired output like
    aa aaa 123
    ab abc 111

    Please any query suggestions?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please give a better example

    your data doesn't make any sense

    neither 123 nor 111 is unique
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2011
    Posts
    3
    Yes neither 111 or 123 is unique. Phonenumber column has duplicate values for these.

    But the desired output is to get records where phonenumber is unique (other columns can have duplicate values as its output).

    There are two (distinct) instances of phonnumber 123 and 111.

    So the output should b like.

    aa aaa 123 (1st occurrence of distinct phonenumber instance 123)
    ab abc 111 (1st occurrence of distinct phonenumber instance 111)

    Regards.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay, this will do the job nicely ...
    Code:
    SELECT MIN(name) AS name
         , MAX(address) AS address
         , phone
      FROM daTable
    GROUP
        BY phone
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2011
    Posts
    3
    Thanks,
    It did the trick, but it has some flaws i think.
    It will change the column values.

    Consider this situation

    a abc 111
    aa abcd 111

    min(name) will give us a, max(address) will give us abcd by grouping on phonenumber 111.

    So finally the record returned will be as

    a abcd 111

    but such row does not exist????

    Any fix for this one????
    Last edited by mca007; 11-19-11 at 01:50. Reason: Corrections....

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mca007 View Post
    but such row does not exist??????
    oh, was that a requirement?

    you have multiple people at multiple addresses with the same phone number, and you want to throw most of that information away, and keep only one person at one address, and you're worried that it has to be an actual row from amongst the duplicates?

    how many total rows are in your table, and how many duplicates are there?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try:
    Code:
    WITH CTE AS
    (SELECT Name, 
    	Address, 
    	Phone, 
    	ROW_NUMBER() OVER (PARTITION BY Phone ORDER BY name, address) as RowNum
    FROM DaTable
    )
    SELECT Name, 
    	Address, 
    	Phone
    FROM CTE
    WHERE RowNum = 1
    aa aaa 123 (1st occurrence of distinct phonenumber instance 123)
    ab abc 111 (1st occurrence of distinct phonenumber instance 111)
    In a relational database there is no such thing as a "first occurrence of ". You have to explicitly tell the RDBMS how it should order your records. I assumed that it was by (name, address). When there is another column that should be used to order the records, you will have to adjust "ORDER BY name, address)" accordingly.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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