Re: [sqlite] Listing duplicate entries
Igor Tandetnik wrote: > > flakpit <[EMAIL PROTECTED]> wrote: >> Is there a way of querying the database to list all duplicate entries >> from a column in the same table? >> >> Something like "SELECT * FROM mytable WHERE last NOT UNIQUE" > > select * from mytable t1 where exists ( > select * from mytable t2 where t1.last=t2.last and t1.rowid != > t2.rowid); > > -- or > > select * from mytable where rowid not in ( > select rowid from mytable > group by last > having count(*) = 1 > ); > > Igor Tandetnik > > Thank you!!! I will have fun trying this out. -- View this message in context: http://www.nabble.com/Listing-duplicate-entries-tp16941525p16951811.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Listing duplicate entries
select * from mytable where last in ( select last from mytable group by last_name having count(*) > 1 ) Probably slow on a big table. -Clark - Original Message From: flakpit <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Monday, April 28, 2008 8:33:36 AM Subject: [sqlite] Listing duplicate entries Is there a way of querying the database to list all duplicate entries from a column in the same table? Something like "SELECT * FROM mytable WHERE last NOT UNIQUE" fred, johnson roger, johnson -- View this message in context: http://www.nabble.com/Listing-duplicate-entries-tp16941525p16941525.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Listing duplicate entries
Hello Igor,
sorry, i was too fast. The following does it:
create table mytable (a string, b string);
insert into mytable (a,b) values ('Joe', 'Smith');
insert into mytable (a,b) values ('Ann', 'Smith');
insert into mytable (a,b) values ('Fred', 'Miller');
select * from mytable where b in
(
select b
from mytable
group by b
having count(b) > 1
)
Igor Tandetnik wrote:
> Martin Engelschalk
> <[EMAIL PROTECTED]> wrote:
>
>> Hi,
>>
>> let the table have two columns, "a" and "b". Then
>>
>> select a, count(b)
>> from yourtable
>> group by a
>> having count(b) > 1
>>
>> returns 'fred' and 'roger'.
>>
>
> No it doesn't. It returns an empty set. Try it.
>
> Igor Tandetnik
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Listing duplicate entries
flakpit wrote: > Is there a way of querying the database to list all duplicate entries from a > column in the same table? > > Something like "SELECT * FROM mytable WHERE last NOT UNIQUE" > > fred, johnson > roger, johnson > > An unoptimised 'off the top of my head' solution would be: select * from mytable where last in (select last from mytable group by last having count(last) > 1); ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Listing duplicate entries
Martin Engelschalk <[EMAIL PROTECTED]> wrote: > Hi, > > let the table have two columns, "a" and "b". Then > > select a, count(b) > from yourtable > group by a > having count(b) > 1 > > returns 'fred' and 'roger'. No it doesn't. It returns an empty set. Try it. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Listing duplicate entries
flakpit <[EMAIL PROTECTED]> wrote: > Is there a way of querying the database to list all duplicate entries > from a column in the same table? > > Something like "SELECT * FROM mytable WHERE last NOT UNIQUE" select * from mytable t1 where exists ( select * from mytable t2 where t1.last=t2.last and t1.rowid != t2.rowid); -- or select * from mytable where rowid not in ( select rowid from mytable group by last having count(*) = 1 ); Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Listing duplicate entries
Hi, let the table have two columns, "a" and "b". Then select a, count(b) from yourtable group by a having count(b) > 1 returns 'fred' and 'roger'. Martin flakpit wrote: > Is there a way of querying the database to list all duplicate entries from a > column in the same table? > > Something like "SELECT * FROM mytable WHERE last NOT UNIQUE" > > fred, johnson > roger, johnson > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Listing duplicate entries
Is there a way of querying the database to list all duplicate entries from a column in the same table? Something like "SELECT * FROM mytable WHERE last NOT UNIQUE" fred, johnson roger, johnson -- View this message in context: http://www.nabble.com/Listing-duplicate-entries-tp16941525p16941525.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
