pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT DISTINCT
7+ messages / 5 participants
[nested] [flat]
* SELECT DISTINCT
@ 1999-01-02 01:34 Tim Perdue, The Des Moines City.net <tim@dmcity.net>
0 siblings, 0 replies; 7+ messages in thread
From: Tim Perdue, The Des Moines City.net @ 1999-01-02 01:34 UTC (permalink / raw)
To: pgsql-sql@hub.org
I have been trying to do a SELECT DISTINCT from a pgsql table and am having
trouble. I've tried subselects, EXISTS, IN, etc, and have had no luck.
I want the query to select all the cities in a state, eliminating
duplicates. I also want the ZIP code (any one of that city's zip codes)
returned in the result set.
select distinct fld_city from tbl_zip_city where fld_state='IA' and
fld_country='us';
That query returns half of what I want (distinct city), but does not return
the associated zip code.
select distinct fld_city, fld_zip from tbl_zip_city where fld_state='IA' and
fld_country='us';
That query does not return distinct cities any more. It returns all cities
(because each city can have several zip codes).
Any suggestions?
Tim
The Des Moines City.net
http://desmoinesnet.com/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] SELECT DISTINCT
@ 1999-01-02 02:40 Peter Garner <peter_garner@yahoo.com>
0 siblings, 0 replies; 7+ messages in thread
From: Peter Garner @ 1999-01-02 02:40 UTC (permalink / raw)
To: pgsql-sql@hub.org
Hi Tim,
> select distinct fld_city, fld_zip from tbl_zip_city where
fld_state='IA' and
> fld_country='us';
>
> That query does not return distinct cities any more. It returns all
cities
> (because each city can have several zip codes).
>
Yes, that is what it is supposed to do, heheheh. Try something like :
select city, min (zip_code)
from tbl_zip_city
group by city
having fld_state = 'IA'
Basically since you have multiple zip codes and want only one per city
you have to tell the sql engine which zip code to pick. It won't just
select one at random. I hope this helps! :-)
==
Peace,
Peter
_________________________________________________________
DO YOU YAHOO!?
Get your free @yahoo.com address at http://mail.yahoo.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] SELECT DISTINCT
@ 1999-01-02 03:15 Tim Perdue, The Des Moines City.net <tim@dmcity.net>
0 siblings, 0 replies; 7+ messages in thread
From: Tim Perdue, The Des Moines City.net @ 1999-01-02 03:15 UTC (permalink / raw)
To: Peter Garner <peter_garner@yahoo.com>; pgsql-sql@hub.org
Thanks - the MIN and GROUP BY did the trick. Here's what I did in the end:
select fld_city, min (fld_zip) from tbl_zip_city where fld_state = 'IA'
group by fld_city;
And now I'm good to go!
Tim
The Des Moines City.net
http://desmoinesnet.com/
-----Original Message-----
From: Peter Garner <peter_garner@yahoo.com>
To: pgsql-sql@hub.org <pgsql-sql@hub.org>
Date: Friday, January 01, 1999 8:46 PM
Subject: Re: [SQL] SELECT DISTINCT
>Hi Tim,
>
>> select distinct fld_city, fld_zip from tbl_zip_city where
>fld_state='IA' and
>> fld_country='us';
>>
>> That query does not return distinct cities any more. It returns all
>cities
>> (because each city can have several zip codes).
>>
>
>Yes, that is what it is supposed to do, heheheh. Try something like :
>
>select city, min (zip_code)
>from tbl_zip_city
>group by city
>having fld_state = 'IA'
>
>Basically since you have multiple zip codes and want only one per city
>you have to tell the sql engine which zip code to pick. It won't just
>select one at random. I hope this helps! :-)
>==
>
>Peace,
>
>Peter
>
>_________________________________________________________
>DO YOU YAHOO!?
>Get your free @yahoo.com address at http://mail.yahoo.com
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* select distinct?
@ 1999-12-08 01:03 Chris Griffin <cgriffin@websales.com>
1999-12-08 01:58 ` Re: [SQL] select distinct? Chris Griffin <cgriffin@websales.com>
0 siblings, 1 reply; 7+ messages in thread
From: Chris Griffin @ 1999-12-08 01:03 UTC (permalink / raw)
To: pgsql-sql
I have two tables. One has job candidate information (name, salary, area of
experience, etc.). The other table contains jobs. I am searching on 4
different fields. I want a list of candidates that match at least one job.
So all 4 fields have to match. Of course I want each candidate only once. I
have tried using distinct on the key field but I get an error. I can't seem
to figure out how to use group by in this situation either. Any help would
be great! Thanks.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: [SQL] select distinct?
1999-12-08 01:03 select distinct? Chris Griffin <cgriffin@websales.com>
@ 1999-12-08 01:58 ` Chris Griffin <cgriffin@websales.com>
0 siblings, 0 replies; 7+ messages in thread
From: Chris Griffin @ 1999-12-08 01:58 UTC (permalink / raw)
To: pgsql-sql
I found the email tom posted on the 4th about this same thing. I tried a
variation on the suggestion in that email and it still complained: "Illegal
use of aggregates or non-group column in target list".
^ permalink raw reply [nested|flat] 7+ messages in thread
* SELECT DISTINCT
@ 2008-02-19 10:01 Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-19 13:51 ` Re: SELECT DISTINCT Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 7+ messages in thread
From: Shavonne Marietta Wijesinghe @ 2008-02-19 10:01 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: SELECT DISTINCT
2008-02-19 10:01 SELECT DISTINCT Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
@ 2008-02-19 13:51 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 7+ messages in thread
From: Richard Huxton @ 2008-02-19 13:51 UTC (permalink / raw)
To: Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>; +Cc: pgsql-sql
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 duplicate value of course
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2008-02-19 13:51 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-01-02 01:34 SELECT DISTINCT Tim Perdue, The Des Moines City.net <tim@dmcity.net>
1999-01-02 02:40 Re: [SQL] SELECT DISTINCT Peter Garner <peter_garner@yahoo.com>
1999-01-02 03:15 Re: [SQL] SELECT DISTINCT Tim Perdue, The Des Moines City.net <tim@dmcity.net>
1999-12-08 01:03 select distinct? Chris Griffin <cgriffin@websales.com>
1999-12-08 01:58 ` Re: [SQL] select distinct? Chris Griffin <cgriffin@websales.com>
2008-02-19 10:01 SELECT DISTINCT Shavonne Marietta Wijesinghe <shavonne.marietta@studioform.it>
2008-02-19 13:51 ` Richard Huxton <dev@archonet.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox