From: | Ragnar <gnari(at)hive(dot)is> |
---|---|
To: | "shyju c(dot)k" <newslttr(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: HOW SELECT |
Date: | 2006-08-25 10:21:50 |
Message-ID: | 1156501311.26837.75.camel@localhost.localdomain |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Mark Matthews | 2006-08-25 10:33:32 | Re: CMS - portal server Question |
Previous Message | David Garamond | 2006-08-25 10:10:26 | preload perl modules in plperl? |