Results 1 to 14 of 14
  1. #1
    Join Date
    Oct 2011
    Location
    Corpus Christi, TX
    Posts
    26

    Unanswered: Distinct Select Query Question

    I have a column with a property and another with the owner of the property. Im using the query below to group the properties by property and owner.

    Select distinct [PROPERTY], [OWNER]
    from [PRODUCTION 2007SQL].dbo.[2007]
    order by [PROPERTY], [OWNER]

    Results:
    PROPERTY OWNER
    1 97994 81579
    2 97994 668921
    3 97994 684446
    4 97994 744714
    5 97994 744751
    6 45 886242
    7 58 886275

    Question: How can I ommit the properties that only have one owner (line items 6&7? My goal is to isolate the properties that have changed ownership one or more times.

  2. #2
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Have you tried . . .

    Code:
    select  property
            ,count(*) NumOfOwners
    from    dbo.[2007]
    group
    by      property
    having  count(*)>1
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  3. #3
    Join Date
    Oct 2011
    Location
    Corpus Christi, TX
    Posts
    26
    I just ran it. Its counting the Owner column.

    I forgot to mention. Each owner submits a monthly report for each item. The only way I can tell the item has changed ownership is if they submit a monthly report and the owner number has changed.

  4. #4
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    It may be counting the owners column, but it also should have excluded the two properties for which the owner did not change.

    So, the query is doing exactly what you wanted it to do, showing you only the properties where the owner did not change. It may have also given you additional information as to the number of owners, but just ignore that additional information (if it is of no interest to you).

    You can now use this as the basis of returning the detailed records associated with those properties.

    Concerning the monthly functionality that you mentioned, you are going to have to supply more info on what is happening. Is there a report date field? Is there a month field? What does the data look like? Etc., Etc., Etc.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT t.property
         , t.owner
      FROM ( SELECT property
               FROM [production 2007sql].dbo.[2007]
             GROUP
                 BY property
             HAVING COUNT(DISTINCT owner) > 1 ) AS m
    INNER
      JOIN [production 2007sql].dbo.[2007] AS t
        ON t.property = m.property
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2011
    Location
    Corpus Christi, TX
    Posts
    26
    Yes. It did exclude the properties that did not change ownership. How can i get it to display the owner #?

    I do have a month and year column. Eventually, I would like to query the items changing ownership and display the first time the new owner reported it.

    Quote Originally Posted by PracticalProgram View Post
    It may be counting the owners column, but it also should have excluded the two properties for which the owner did not change.

    So, the query is doing exactly what you wanted it to do, showing you only the properties where the owner did not change. It may have also given you additional information as to the number of owners, but just ignore that additional information (if it is of no interest to you).

    You can now use this as the basis of returning the detailed records associated with those properties.

    Concerning the monthly functionality that you mentioned, you are going to have to supply more info on what is happening. Is there a report date field? Is there a month field? What does the data look like? Etc., Etc., Etc.
    Last edited by justchillin; 11-01-11 at 18:30.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by justchillin View Post
    How can i get it to display the owner #?
    like this --
    Code:
    SELECT t.property
         , t.owner -- vwalah!!
      FROM ( SELECT property
               FROM [production 2007sql].dbo.[2007]
             GROUP
                 BY property
             HAVING COUNT(DISTINCT owner) > 1 ) AS m
    INNER
      JOIN [production 2007sql].dbo.[2007] AS t
        ON t.property = m.property
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Wow r937.

    That's much cleaner.

    I've never thought of using the HAVING clause without an aggregate in the SELECT clause.

    Learn something new every day.

    Thanks.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  9. #9
    Join Date
    Oct 2011
    Location
    Corpus Christi, TX
    Posts
    26
    Wow r937 & PracticalProgram:

    Thanks for the help. I was able to get the results I needed by using both queries. I dont quite grasp what Im doing yet. Any recommendations on how to learn the basics first?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by justchillin View Post
    I was able to get the results I needed by using both queries.
    huh?

    why would you need both queries?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    How do you learn?

    Just do a lot of it.

    And read other people's code and pick up ideas from them. In this one thread, I was exposed to something new that I will start using.

    And simplify. Always look to see if you can do it more simply--don't write miles of code.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by justchillin View Post
    Any recommendations on how to learn the basics first?
    Click the link in r937's signature.

    Quote Originally Posted by r937 View Post
    why would you need both queries?
    I think that justchillin means that both queries worked (and produced the same results), not that both queries were needed.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    ...both queries worked (and produced the same results)
    but that's just it -- they ~don't~ produce the same results
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  14. #14
    Join Date
    Oct 2011
    Location
    Corpus Christi, TX
    Posts
    26
    Meaning I used them seperately. I have a future use for the count query so it was very helpful.

Posting Permissions

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