Thread: 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