How to get only repeated records in a table?

Question

I couldn't figure out how better to ask this question, so my searches became desperate.

I have a table with three columns(Column1, Column2, Column3). There are lots of records consisted of different and same values in it. I want to get only the exactly same records as column1, column2 and column3 values. How can I get them in the fastest query in general SQL? And especially HSQLDB?

Besides, if my table has 4 columns (+ Column4) but still I need same records of column1,column2 and column3 values. Query must change or will be same?

Example;

-------------------------------
| Column1 | Column2 | Column3 |
|    1    |    2    |   3     |  <-- A
|    2    |    2    |   30    |  <-- B
|    3    |    3    |   10    |
|    4    |    12   |   3     |  <-- C
|    1    |    3    |   3     |
|    1    |    4    |   3     |
|    1    |    5    |   3     |
|    4    |    12   |   3     |  <-- C
|    2    |    2    |   30    |  <-- B
|    1    |    2    |   3     |  <-- A
|    4    |    12   |   3     |  <-- C
-------------------------------

So I need to select all A,B and C records. The result should be;

-------------------------------
| Column1 | Column2 | Column3 |
|    1    |    2    |   3     |  <-- A
|    2    |    2    |   30    |  <-- B
|    4    |    12   |   3     |  <-- C
|    4    |    12   |   3     |  <-- C
|    2    |    2    |   30    |  <-- B
|    1    |    2    |   3     |  <-- A
|    4    |    12   |   3     |  <-- C
-------------------------------

-Result omitted completely different records-

rabudde rabudde · Accepted Answer · 2011-08-18T10:02:28

Get them by grouping in combination with a HAVING clause. This should also work in HSQLDB. The HAVING clause removes all data from result a rows isn't contained multiple. When you have a table with 4 columns you have to adopt the statement.

SELECT Column1,Column2,Column3 FROM Table GROUP BY Column1,Column2,Column3 HAVING COUNT(*)>1

EDIT: edited to get all rows

SELECT x.* FROM Table x INNER JOIN (
    SELECT Column1,Column2,Column3 FROM Table GROUP BY Column1,Column2,Column3 HAVING COUNT(*)>1
) y ON x.Column1=y.Column1 AND x.Column2=y.Column2 AND x.Column3=y.Column3

How to get only repeated records in a table?

2 Answers