Need SQL help?

Question

I want to return all results from a table that meet this conditional:

COLUMN-A has 5 rows.
But, for these results, COLUMN-B must also be a distinct value.
So for instance, in the following example, I want to get back a value of "1", because column-A has 5 rows and column-B has the same distinct value. Column-A is ignored for "2", because there aren't the same value for Column-B.

TABLE
-----
COLUMN-A COLUMN-B
-------- --------
1 8
1 8
1 8
1 8
1 8
2 9
2 9
2 9
2 7
2 7

I thought the key would be to write a query like this:

select * from table
where column-A in(select column-A from table group by column-A having count(column-A) > 5
and
column-B in(select column-B from table group by column-B having count(column-B) > 5)
)

But this doesn't work.

Probably something goofy I'm missing.

I'd appreciate a hand.

thanks!

I meant to write >= 5 in the query. That's how it should read.

Answer 1

SELECT DISTINCT ColA FROM tbl a WHERE (SELECT COUNT(*) FROM tbl b WHERE b.ColA = a.ColA AND b.ColB = a.ColB)>=5

Answer 2

I'm not exactly sure what you are trying to do, because I don't think your table is formatted properly in your question.

Do you know about the DISTINCT function?

SELECT DISTINCT column-a FROM table WHERE column-a >= 5 AND column-b >=5;

Answer 3

SELECT Table1.colA
FROM Table1
GROUP BY Table1.colA, Table1.colB
HAVING (((Count(Table1.colB))=5));

Grouping multiple columns will create distinct grouping based upon the unique combination of BOTH columns. Using your supplied data only "1" is returned as it is the only Group which returns a count or the colB equal to 5

Answer 4

select [column-A] from table
group by [column-A], [column-B] having count(*) >= 5

Answer 5

A varchar2 is a variable-length column. varchar2 (4000) potential the highest length value which would be saved interior the sector is 4000 characters. For the day of the week, opt for TO_CHAR(someDate, 'DAY')