On fös, 2006-08-25 at 13:20 +0530, shyju c.k wrote:
> hai all
>
>
> i have table ,as follows
[reformatted]
> id_int | vid_int | name_chv | address_txt
> 2 | 12 | ram | address1
> 3 | 12 | joy | address2
> 4 | 14 | shyju | address3
> 5 | 14 | shyju | address4
> 6 | 30 | thomas | address5
> 7 | 30 | muhamd | address6
> 8 | 30 | rahim | address7
>
>
> here only vid_int=14 , have name=shyju repeated
> [ how query for list the following records ]
> 4 | 14 | shyju | address3
> 5 | 14 | shyju | address4
if I understand you correctly, you want
duplicate (vid_int,name_chv).
to just find the duplicated values, you could do:
SELECT vid_int,name_chv
FROM mytable
GROUP BY vid_int,name_chv
HAVING count(*) > 1;
to get the full rows, you could for example
do a join to this.
SELECT t.*
FROM mytable AS t
NATURAL JOIN
( SELECT vid_int,name_chv
FROM mytable
GROUP BY vid_int,name_chv
HAVING count(*) > 1
) AS g;
test=# create table mytable (id_int int,vid_int int, name_chv text,
address_txt text);
CREATE TABLE
test=# insert into mytable VALUES (2,12,'ram','address1');
INSERT 34480915 1
test=# insert into mytable VALUES (3,12,'joy','address2');
INSERT 34480916 1
test=# insert into mytable VALUES (4,14,'shyju','address3');
INSERT 34480917 1
test=# insert into mytable VALUES (5,14,'shyju','address4');
INSERT 34480918 1
test=# insert into mytable VALUES (6,30,'thomas','address5');
INSERT 34480919 1
test=# insert into mytable VALUES (7,30,'muhamd','address6');
INSERT 34480920 1
test=# insert into mytable VALUES (8,30,'rahim','address7');
INSERT 34480921 1
test=# select * from mytable;
id_int | vid_int | name_chv | address_txt
--------+---------+----------+-------------
2 | 12 | ram | address1
3 | 12 | joy | address2
4 | 14 | shyju | address3
5 | 14 | shyju | address4
6 | 30 | thomas | address5
7 | 30 | muhamd | address6
8 | 30 | rahim | address7
(7 rows)
test=# SELECT t.*
test-# FROM mytable AS t
test-# NATURAL JOIN
test-# ( SELECT vid_int,name_chv
test(# FROM mytable
test(# GROUP BY vid_int,name_chv
test(# HAVING count(*) > 1
test(# ) AS g;
id_int | vid_int | name_chv | address_txt
--------+---------+----------+-------------
4 | 14 | shyju | address3
5 | 14 | shyju | address4
(2 rows)
hope this helps.
gnari
