Thread: SELECT DISTINCT

SELECT DISTINCT

From

"Shavonne Marietta Wijesinghe"

Date:

19 February 2008, 09:01:32

Hello

I have 2 records with the same value. Even when i do a select with DISTINCT, it returns me both the records instead of one.

SELECT DISTINCT ON (TE_COGNOME) TE_COGNOME, TE_NOME, N_GEN_TEST, TE_SESSO, TE_ATTNASC, TE_LUONASC, TE_INDI, TE_DTNASC, TE_PROVSTATO, TE_PROV, TE_PATERNITA, TE_RICHIESTA FROM MOD48_02 WHERE TE_COGNOME LIKE 'WIJ%' AND TRIM(DELETED) IS NULL ORDER BY TE_COGNOME, N_GEN_TEST DESC;

What should I do??

Thanks

Shavonne

Re: SELECT DISTINCT

From

Richard Huxton

Date:

19 February 2008, 12:51:37

Shavonne Marietta Wijesinghe wrote:
> Hello
> 
> I have 2 records with the same value. Even when i do a select with
> DISTINCT, it returns me both the records instead of one.
> 
> SELECT DISTINCT ON (TE_COGNOME) TE_COGNOME, TE_NOME, N_GEN_TEST,
> TE_SESSO, TE_ATTNASC, TE_LUONASC, TE_INDI, TE_DTNASC, TE_PROVSTATO,
> TE_PROV, TE_PATERNITA, TE_RICHIESTA FROM MOD48_02 WHERE TE_COGNOME
> LIKE 'WIJ%' AND TRIM(DELETED) IS NULL ORDER BY TE_COGNOME, N_GEN_TEST
> DESC;

Can you provide:
1. The output of your query (just the duplicate rows will be fine)
2. The output of "\d MOD48_02"?
3. The output of:   SELECT xmin,xmax, ':' || TE_COGNOME || ':' FROM MOD48_02   WHERE TE_COGNOME = ???   For the
duplicatevalue of course

--   Richard Huxton  Archonet Ltd