Re: HOW SELECT - Mailing list pgsql-general

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