Обсуждение: A select DISTINCT query?
People, I want to select from a table ONLY unique records ie if a column has values: 1 2 3 3 4 5 I want ONLY these records returned: 1 2 4 5 Thanks, Phil. -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: phil@pricom.com.au
Hello try SELECT DISTINCT col FROM table Pavel On 27/01/2008, Phil Rhoades <phil@pricom.com.au> wrote: > People, > > I want to select from a table ONLY unique records ie if a column has > values: > > 1 > 2 > 3 > 3 > 4 > 5 > > I want ONLY these records returned: > > 1 > 2 > 4 > 5 > > Thanks, > > Phil. > -- > Philip Rhoades > > Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) > GPO Box 3411 > Sydney NSW 2001 > Australia > Fax: +61:(0)2-8221-9599 > E-mail: phil@pricom.com.au > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq >
Phil Rhoades wrote: > People, > > I want to select from a table ONLY unique records ie if a column has > values: > > 1 > 2 > 3 > 3 > 4 > 5 > > I want ONLY these records returned: > > 1 > 2 > 4 > 5 SELECT count(*) as cnt,a,b,c FORM yourtable GROUP BY a,b,c HAVING cnt=1 should do. Regards Tino
Pavel, You didn't read my note properly - your query gives: 1 2 3 4 5 I want: 1 2 4 5 Phil. On Sun, 2008-01-27 at 15:10 +0100, Pavel Stehule wrote: > Hello > > try > > SELECT DISTINCT col FROM table > > Pavel > > On 27/01/2008, Phil Rhoades <phil@pricom.com.au> wrote: > > People, > > > > I want to select from a table ONLY unique records ie if a column has > > values: > > > > 1 > > 2 > > 3 > > 3 > > 4 > > 5 > > > > I want ONLY these records returned: > > > > 1 > > 2 > > 4 > > 5 > > > > Thanks, > > > > Phil. > > -- > > Philip Rhoades > > > > Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) > > GPO Box 3411 > > Sydney NSW 2001 > > Australia > > Fax: +61:(0)2-8221-9599 > > E-mail: phil@pricom.com.au > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster > -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: phil@pricom.com.au
Tino,
On Sun, 2008-01-27 at 15:16 +0100, Tino Wildenhain wrote:
> Phil Rhoades wrote:
> > People,
> >
> > I want to select from a table ONLY unique records ie if a column has
> > values:
> >
> > 1
> > 2
> > 3
> > 3
> > 4
> > 5
> >
> > I want ONLY these records returned:
> >
> > 1
> > 2
> > 4
> > 5
>
>
> SELECT count(*) as cnt,a,b,c FORM yourtable
> GROUP BY a,b,c
> HAVING cnt=1
>
> should do.
I get:
SELECT count(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ;
ERROR: column "cnt" does not exist
LINE 1: ...ount(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ;
^
Thanks,
Phil.
--
Philip Rhoades
Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275)
GPO Box 3411
Sydney NSW 2001
Australia
Fax: +61:(0)2-8221-9599
E-mail: phil@pricom.com.au
On Mon, Jan 28, 2008 at 03:32:18AM +1100, Phil Rhoades wrote: > SELECT count(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ; > ERROR: column "cnt" does not exist > LINE 1: ...ount(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ; having count(*) = 1; depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV)
On 27/01/2008, Phil Rhoades <phil@pricom.com.au> wrote: > Tino, > > > On Sun, 2008-01-27 at 15:16 +0100, Tino Wildenhain wrote: > > Phil Rhoades wrote: > > > People, > > > > > > I want to select from a table ONLY unique records ie if a column has > > > values: > > > > > > 1 > > > 2 > > > 3 > > > 3 > > > 4 > > > 5 > > > > > > I want ONLY these records returned: > > > > > > 1 > > > 2 > > > 4 > > > 5 > > > > > > SELECT count(*) as cnt,a,b,c FORM yourtable > > GROUP BY a,b,c > > HAVING cnt=1 > > > > should do. > > > I get: > > SELECT count(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ; > ERROR: column "cnt" does not exist > LINE 1: ...ount(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ; > ^ > select count(*) as cnt, name from tst group by name having count(*) = 1 > Thanks, > > Phil. > -- > Philip Rhoades > > Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) > GPO Box 3411 > Sydney NSW 2001 > Australia > Fax: +61:(0)2-8221-9599 > E-mail: phil@pricom.com.au > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Guys, On Sun, 2008-01-27 at 17:38 +0100, Pavel Stehule wrote: > On 27/01/2008, Phil Rhoades <phil@pricom.com.au> wrote: > > Tino, > > > > > > On Sun, 2008-01-27 at 15:16 +0100, Tino Wildenhain wrote: > > > Phil Rhoades wrote: > > > > People, > > > > > > > > I want to select from a table ONLY unique records ie if a column has > > > > values: > > > > > > > > 1 > > > > 2 > > > > 3 > > > > 3 > > > > 4 > > > > 5 > > > > > > > > I want ONLY these records returned: > > > > > > > > 1 > > > > 2 > > > > 4 > > > > 5 > > > > > > > > > SELECT count(*) as cnt,a,b,c FORM yourtable > > > GROUP BY a,b,c > > > HAVING cnt=1 > > > > > > should do. > > > > > > I get: > > > > SELECT count(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ; > > ERROR: column "cnt" does not exist > > LINE 1: ...ount(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ; > > ^ > > > select count(*) as cnt, name from tst group by name having count(*) = 1 Muchas gracias! Phil. -- Philip Rhoades Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275) GPO Box 3411 Sydney NSW 2001 Australia Fax: +61:(0)2-8221-9599 E-mail: phil@pricom.com.au