Thread: How to find records with the same field?

I have a table with column1, column2, column3 and column4. How do I get all records, sorted by column4 that have the
samecolumn1,column2 and column3?
 

TIA

 

Joost

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


Hi,

On Tue, 20 Jul 2004, Joost Kraaijeveld wrote:

> I have a table with column1, column2, column3 and column4. How do I get
> all records, sorted by column4 that have the same column1,column2 and
> column3?

SELECT * from table_name WHERE (c1=c2) AND (c2=c3) ORDER BY c4;

will work, I think:
===================
test=> CREATE TABLE joost (c1 varchar(10), c2 varchar(10), c3 varchar(10),
c4 varchar(10));
CREATE TABLE
test=> INSERT INTO joost VALUES ('test1','test1','test1','remark');
INSERT 1179458 1
test=> INSERT INTO joost VALUES ('test1','test1','test1','remark2');
INSERT 1179459 1
test=> INSERT INTO joost VALUES ('test1','test2','test3','nevermind');
INSERT 1179460 1
test=> SELECT * from joost ;
  c1   |  c2   |  c3   |    c4
- -------+-------+-------+-----------
 test1 | test1 | test1 | remark
 test1 | test1 | test1 | remark2
 test1 | test2 | test3 | nevermind
(3 rows)

test=> SELECT * from joost WHERE (c1=c2) AND (c2=c3) ORDER BY c4
test-> ;
  c1   |  c2   |  c3   |   c4
- -------+-------+-------+--------
 test1 | test1 | test1 | remark
 test1 | test1 | test1 | remark
(2 rows)
===================

Regards,

- --
Devrim GUNDUZ
devrim~gunduz.org                devrim.gunduz~linux.org.tr
            http://www.tdmsoft.com
            http://www.gunduz.org
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (GNU/Linux)

iD8DBQFA/OoHtl86P3SPfQ4RAqdmAKDVyBy6LFR1zFk4phuZnkHdaOk4SACaAwz9
JUhJUBtGoabox8VG9EpTkBQ=
=SfQ5
-----END PGP SIGNATURE-----

Hi joost,

I think the following should work:

include the table 2 times in your query and join the two instances in
the query by the 3 columns.

Example:

Select
  t1. column4, t1.column1, t1.column2, t1.column3
From
  yourtable t1, yourtable t2
Where
  t1.column1 = t2.column1
    and t1.column2 = t2.column2
    and t1.column3 = t2.column3
order by
  t1.column4;

>-----Ursprüngliche Nachricht-----
>Von: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
>owner@postgresql.org] Im Auftrag von Joost Kraaijeveld
>Gesendet: Dienstag, 20. Juli 2004 11:39
>An: pgsql-general@postgresql.org
>Betreff: [GENERAL] How to find records with the same field?
>
>I have a table with column1, column2, column3 and column4. How do I get
all
>records, sorted by column4 that have the same column1,column2 and
column3?
>
>TIA
>
>
>
>Joost
>
>
>---------------------------(end of
broadcast)---------------------------TIP
>9: the planner will ignore your desire to choose an index scan if your
>joining column's datatypes do not match

Thanks everyone for answering. Apparently my question was not clear enough.
 
I want something like this:
 
select col1, col2,col3, col4 
from table1
where
col1 =col1 and
col2 = col2 and
col3 = col3
order by col4
 
But if I run this query I get all the records in the table and not just the (double) ones with the same columns.
 
TIA
 
Joost
 

You can find the duplicates with a self-join:

select t1.col1, t1.col2, t1.col3, t1.col4
  from table1 as t1, table1 as t2
  where
  t1.oid != t2.oid
  t1.col1 = t2.col1 and
  t1.col2 = t2.col2 and
  t1.col3 = t2.col3
  order by t1.col4;



Joost Kraaijeveld schrieb:
> Thanks everyone for answering. Apparently my question was not clear enough.
>
> I want something like this:
>
> select col1, col2,col3, col4
> from table1
> where
> col1 =col1 and
> col2 = col2 and
> col3 = col3
> order by col4
>
> But if I run this query I get all the records in the table and not just the (double) ones with the same columns.
>
> TIA
>
> Joost
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org

Sorry, iI frogot an "and" after "1.oid != t2.oid"

select t1.col1, t1.col2, t1.col3, t1.col4
   from table1 as t1, table1 as t2
   where
   t1.oid != t2.oid AND
   t1.col1 = t2.col1 and
   t1.col2 = t2.col2 and
   t1.col3 = t2.col3
   order by t1.col4;
>
>
>
> Joost Kraaijeveld schrieb:
>
>>Thanks everyone for answering. Apparently my question was not clear enough.
>>
>>I want something like this:
>>
>>select col1, col2,col3, col4
>>from table1
>>where
>>col1 =col1 and
>>col2 = col2 and
>>col3 = col3
>>order by col4
>>
>>But if I run this query I get all the records in the table and not just the (double) ones with the same columns.
>>
>>TIA
>>
>>Joost
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: Have you searched our list archives?
>>
>>               http://archives.postgresql.org
>
>
>