A select DISTINCT query?

Hello

try

SELECT DISTINCT col FROM table

Pavel

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

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