Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2012
    Posts
    6

    Select repeated values

    What I'd like to do is to identify whether values are repeated in a column, if certain other conditions are also met. Like this:

    SELECT DISTINCT A, B FROM TABLE WHERE (C>'' AND B='') GROUP BY B HAVING COUNT(*) > 1

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    if you already know that B is going to be equal to '', why are you grouping on it?

    could you repeat the question please?

    perhaps with better sample data
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2012
    Posts
    6

    Ok

    Column A holds names, B holds numeric values, and C holds dates.

    I want to select distinct names when the date has any value and when column B has no numeric value on more than one occasion.

    I want to group by column B if the values = '', because there will be a number of them with different values but the only important thing for this report is when column B is blank.

    Here's what I have tried:

    SELECT DISTINCT A, B FROM TABLE WHERE (C>'' AND B='') GROUP BY B HAVING COUNT(*) > 1

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i'm sorry, i'm having a hard time understanding you

    if B holds numeric values, why are you comparing it to a string? and if C holds dates, why are you comparing it to a string?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2012
    Posts
    6
    The date is in a 16 character string used for other comparisons. The other can have a null value, which is what it should be checked against.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    checking for null should use IS NULL, not a string comparison
    Code:
    SELECT a
      FROM daTable
     WHERE b IS NULL
       AND C > ''
    GROUP
        BY a
    HAVING COUNT(*) > 1
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jul 2012
    Posts
    1
    Quote Originally Posted by r937 View Post
    checking for null should use IS NULL, not a string comparison
    Code:
    SELECT a
      FROM daTable
     WHERE b IS NULL
       AND C > ''
    GROUP
        BY a
    HAVING COUNT(*) > 1
    Thank you! I had the same problem and couldn't figure out what I was doing wrong. The string comparison confused me

Tags for this Thread

Posting Permissions

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