Re: [sqlite] Select rows where a column is not unique

[Igor Tandetnik]

On 8/6/2012 9:24 AM, Rob Richardson wrote:

Which of those would be fastest?  Or don't you have enough information to tell?


Hard for me to predict. If it were important to me, I'd test and time 
all of them on a realistic dataset.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select rows where a column is not unique

[Tilsley, Jerry M.]

Thanks all for quick response.  I was able to eliminate my duplicates very easy!

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Paul van Helden
Sent: Monday, August 06, 2012 9:15 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Select rows where a column is not unique

On Mon, Aug 6, 2012 at 2:58 PM, Tilsley, Jerry M.
<jmtils...@st-claire.org>wrote:

> Guys,
>
> I'm sure this is a pretty lame question, but my thinking hat is
> malfunctioning this morning.  How can I select all rows from a table
> where a specific column is NOT UNIQUE?  Table has three columns
> (charge_code, mnemonic, description).
>
>
SELECT * FROM tablename WHERE charge_code IN (SELECT charge_code FROM tablename 
GROUP BY description HAVING Count(*)>1) 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users



Disclaimer
This email is confidential and intended solely for the use of the individual to 
whom it is addressed. Any views or opinions presented are solely those of the 
author and do not necessarily represent those of St. Claire Regional Medical 
Center. If you are not the intended recipient, be advised that you have 
received this email in error and that any use, dissemination, forwarding, 
printing or copying of the email is strictly prohibited. If you received this 
email in error please notify the St. Claire Regional Helpdesk by telephone at 
606-783-6565.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select rows where a column is not unique

[Rob Richardson]

Igor,

Which of those would be fastest?  Or don't you have enough information to tell?

RobR

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Igor Tandetnik
Sent: Monday, August 06, 2012 9:14 AM
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Select rows where a column is not unique

Tilsley, Jerry M. <jmtils...@st-claire.org> wrote:
> I'm sure this is a pretty lame question, but my thinking hat is 
> malfunctioning this morning.  How can I select all rows from a table where a 
> specific column is NOT UNIQUE?

select * from MyTable where SpecificColumn in (
  select SpecificColumn from MyTable
  group by SpecificColumn
  having count(*) > 1);

-- or

select * from MyTable t1 where exists (
  select 1 from MyTable t2
  where t2.SpecificColumn = t1.SpecificColumn and t2.rowid != t1.rowid);

-- or

select * from MyTable t1 where 1 < (
  select count(*) from MyTable t2
  where t2.SpecificColumn = t1.SpecificColumn);

--
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] Select rows where a column is not unique

[Paul van Helden]

On Mon, Aug 6, 2012 at 2:58 PM, Tilsley, Jerry M.
wrote:

> Guys,
>
> I'm sure this is a pretty lame question, but my thinking hat is
> malfunctioning this morning.  How can I select all rows from a table where
> a specific column is NOT UNIQUE?  Table has three columns (charge_code,
> mnemonic, description).
>
>
SELECT * FROM tablename WHERE charge_code IN (SELECT charge_code FROM
tablename GROUP BY description HAVING Count(*)>1)
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Select rows where a column is not unique

[Igor Tandetnik]

Tilsley, Jerry M.  wrote:
> I'm sure this is a pretty lame question, but my thinking hat is 
> malfunctioning this morning.  How can I select all rows from a
> table where a specific column is NOT UNIQUE?

select * from MyTable where SpecificColumn in (
  select SpecificColumn from MyTable
  group by SpecificColumn
  having count(*) > 1);

-- or

select * from MyTable t1 where exists (
  select 1 from MyTable t2
  where t2.SpecificColumn = t1.SpecificColumn and t2.rowid != t1.rowid);

-- or

select * from MyTable t1 where 1 < (
  select count(*) from MyTable t2
  where t2.SpecificColumn = t1.SpecificColumn);

-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Select rows where a column is not unique

[Tilsley, Jerry M.]

Guys,

I'm sure this is a pretty lame question, but my thinking hat is malfunctioning 
this morning.  How can I select all rows from a table where a specific column 
is NOT UNIQUE?  Table has three columns (charge_code, mnemonic, description).

Thanks,


Jerry Tilsley
Sr Systems Analyst
St. Claire Regional Medical Center




Disclaimer
This email is confidential and intended solely for the use of the individual to 
whom it is addressed. Any views or opinions presented are solely those of the 
author and do not necessarily represent those of St. Claire Regional Medical 
Center. If you are not the intended recipient, be advised that you have 
received this email in error and that any use, dissemination, forwarding, 
printing or copying of the email is strictly prohibited. If you received this 
email in error please notify the St. Claire Regional Helpdesk by telephone at 
606-783-6565.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users