Reverse of 'distinct' in sql

[javapenguin]

---

Mga brod expert sa MySQL, what is the reverse of distinct?

Sample input
AAA
BBB
CCC
CCC
DDD
AAA
AAA

Output:
AAA
CCC

I got a solution but I'm not satisfied with performance coz I'm using sub select & I got lots of records, what is your solution to this?

[dodie]

I think CARTESIAN PRODUCT perhaps i'm not sure which part you meant is. That's related to reduntant results on SQL queries.


It's more likely that you should combine DISTINCT query and WHERE statement with using combine
like '%something%' or '_something_'

If it is under a GROUP or related contents, try use WHERE agains it.

If it doesn't work, Find a nother way and querying and rely on it's Foreign Keys.

[BadDudes]

@javapenguin .. wa ko ka gets by default DISTINCT results would be

AAA
BBB
CCC
DDD

right? however you only want these 2 records?

AAA
CCC

to clarify things up, you only need to display records having 2 or more occurrences?

[javapenguin]

@javapenguin .. wa ko ka gets by default DISTINCT results would be

AAA
BBB
CCC
DDD

right? however you only want these 2 records?

AAA
CCC

to clarify things up, you only need to display records having 2 or more occurrences?

You are correct, I only need records having 2 or more occurrences, those that are unique must not be displayed, so I need something that is the reverse of DISTINCT. I got a bad solution and I'm looking for a better one, preferably no sub select.

[moz_k2]

Not sure on this one

SELECT field1, COUNT(*)
 FROM table1
 GROUP BY field1
 HAVING COUNT(*) > 1

if you have to use order by it comes after Having keyword

[javapenguin]

Not sure on this one

SELECT field1, COUNT(*)
 FROM table1
 GROUP BY field1
 HAVING COUNT(*) > 1

if you have to use order by it comes after Having keyword

Yes! This will surely work! I keep thinking what I've been missing. It was the HAVING keyword.

Thanks a lot!

[dodie]

Yes! This will surely work! I keep thinking what I've been missing. It was the HAVING keyword.

Thanks a lot!

Yeheyyyyy!!!! always pair the HAVING and GROUP.
HAVING might not work without the GROUP magic word

[BadDudes]

Not sure on this one

SELECT field1, COUNT(*)
 FROM table1
 GROUP BY field1
 HAVING COUNT(*) > 1

if you have to use order by it comes after Having keyword

definitely this would be your solution

[dlanyer77]

The solution is correct but the question is incorrect. The solution is not the reverse or more appropriately saying inverse of DISTINCT.

Let it be written that WHERE clause filters the rows, then the remaining rows are grouped into blocks by the GROUP BY clause, finally the row groups are filtered by the HAVING clause. This enters the solution, HAVING clause to filter the row groups to those greater than one (>1) and that makes the solution correct.

On the other hand, simply remove the DISTINCT in the SELECT statement and that's the inverse of DISTINCT but surely that's not the solution.

[javapenguin]

The solution is correct but the question is incorrect. The solution is not the reverse or more appropriately saying inverse of DISTINCT.

Let it be written that WHERE clause filters the rows, then the remaining rows are grouped into blocks by the GROUP BY clause, finally the row groups are filtered by the HAVING clause. This enters the solution, HAVING clause to filter the row groups to those greater than one (>1) and that makes the solution correct.

On the other hand, simply remove the DISTINCT in the SELECT statement and that's the inverse of DISTINCT but surely that's not the solution.

Good point, thanks for the correction