How to find records with the same field?

[Joost Kraaijeveld]

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

[Devrim GUNDUZ]

-----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/OoHtl86P3SPfQ4RAqdmAKDVyBy6LFR1zFk4phuZnkHdaOk4SAC aAwz9
JUhJUBtGoabox8VG9EpTkBQ=
=SfQ5
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Peter Alberer]

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: pg*****************@postgresql.org [mailto:pgsql-general-
ow***@postgresql.org] Im Auftrag von Joost Kraaijeveld
Gesendet: Dienstag, 20. Juli 2004 11:39
An: pg***********@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 allrecords, sorted by column4 that have the same column1,column2 and column3?
TIA

Joost
---------------------------(end of broadcast)---------------------------TIP9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly