Select repeated values

Rikard · #1 (**permalink**) 04-29-12, 15:22

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

r937 · #2 (**permalink**) 04-29-12, 16:41

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

Rikard · #3 (**permalink**) 04-29-12, 17:09

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

r937 · #4 (**permalink**) 04-29-12, 17:42

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?

Rikard · #5 (**permalink**) 04-29-12, 22:02

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.

r937 · #6 (**permalink**) 04-29-12, 22:15

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

shanevic7 · #7 (**permalink**) 07-31-12, 09:28

Quote:

Originally Posted by r937

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

Internet Services

Web Development

Digital Marketing

Consumer Tech