pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDistinct Values
6+ messages / 5 participants
[nested] [flat]
* Distinct Values
@ 2001-05-30 18:22 Nicolas <bnk@medialife.net>
2001-06-05 09:18 ` Re: Distinct Values Hans-Jürgen Schönig <hs@cybertec.at>
2001-06-05 09:20 ` Re: Distinct Values Hans-Jürgen Schönig <hs@cybertec.at>
2001-06-05 21:14 ` Re: Distinct Values Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 3 replies; 6+ messages in thread
From: Nicolas @ 2001-05-30 18:22 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'm trying to retreive DISTINCT Values from a two colomn table called
"Books". The colomns are named "Author" and "URL".
DISTINCT values should be retieved from the "Author" Colomn , and then I
should be able to retrieve the corresponding URL.
How do I build the SQL Query ?
I tried to use: "SELECT DISTINCT Author FROM Books" But this does not allow
me to fetch the URL value on the other colomn.
Thanks
Nicolas
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Distinct Values
2001-05-30 18:22 Distinct Values Nicolas <bnk@medialife.net>
@ 2001-06-05 09:18 ` Hans-Jürgen Schönig <hs@cybertec.at>
2 siblings, 0 replies; 6+ messages in thread
From: Hans-Jürgen Schönig @ 2001-06-05 09:18 UTC (permalink / raw)
To: pgsql-sql
Nicolas schrieb:
> Hi,
>
> I'm trying to retreive DISTINCT Values from a two colomn table called
> "Books". The colomns are named "Author" and "URL".
> DISTINCT values should be retieved from the "Author" Colomn , and then I
> should be able to retrieve the corresponding URL.
> How do I build the SQL Query ?
> I tried to use: "SELECT DISTINCT Author FROM Books" But this does not allow
> me to fetch the URL value on the other colomn.
>
> Thanks
> Nicolas
The problem is that one author may have multiple urls - if you want to select
the url too, the database does not know which url to retrieve.
Hans
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Distinct Values
2001-05-30 18:22 Distinct Values Nicolas <bnk@medialife.net>
@ 2001-06-05 09:20 ` Hans-Jürgen Schönig <hs@cybertec.at>
2 siblings, 0 replies; 6+ messages in thread
From: Hans-Jürgen Schönig @ 2001-06-05 09:20 UTC (permalink / raw)
To: pgsql-sql
I want a "dirty" solution try something like that. If you use "group by
author" url must be in aggregate function. The authors are now DISTINCT.
myjava=# SELECT author, max(url) FROM books group by author;
author | max
--------+---------
abc | dsafsdf
cde | sdfdsf
(2 rows)
Hans
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Distinct Values
2001-05-30 18:22 Distinct Values Nicolas <bnk@medialife.net>
@ 2001-06-05 21:14 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2 siblings, 0 replies; 6+ messages in thread
From: Stephan Szabo @ 2001-06-05 21:14 UTC (permalink / raw)
To: Nicolas <bnk@medialife.net>; +Cc: pgsql-sql
On Wed, 30 May 2001, Nicolas wrote:
> Hi,
>
> I'm trying to retreive DISTINCT Values from a two colomn table called
> "Books". The colomns are named "Author" and "URL".
> DISTINCT values should be retieved from the "Author" Colomn , and then I
> should be able to retrieve the corresponding URL.
> How do I build the SQL Query ?
> I tried to use: "SELECT DISTINCT Author FROM Books" But this does not allow
> me to fetch the URL value on the other colomn.
If there is only one distinct url for each author then:
select distinct author, url from books;
If not, *which* url do you want to get?
If it doesn't matter, you can use a non-standard structure:
select distinct on (author) author, url;
If you wanted (for example) the url that could be considered
the minimum, you could do something like:
select author, min(url) from books group by author;
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: Distinct Values
@ 2001-06-06 06:43 Hartwig Jens <Jens.Hartwig@t-systems.de>
2001-06-06 15:23 ` RE: Distinct Values KuroiNeko <evpopkov@carrier.kiev.ua>
0 siblings, 1 reply; 6+ messages in thread
From: Hartwig Jens @ 2001-06-06 06:43 UTC (permalink / raw)
To: Nicolas <bnk@medialife.net>; pgsql-sql
Hello Nicolas,
the query you want to construct cannot work because the result of a
query always consists of a consistent table. That means that in a
two-column-table every item on the "left side" has one single opponent
on "the right side". How should your table be built up at all?
I suppose that you want something like the following:
Author URL
------- --------
A http://www.xyz.com
http://www.abc.com
- -
B http://www.def.com
http://www.ijk.com
http://www.rst.com
- -
If that is what you want, you cannot manage it.
Hope this helps ... although it does not really help you :-)
Best regards, Jens Hartwig
-----------------------------------------------------
T-Systems
Project Manager
debis Systemhaus GEI GmbH
Address: Eichhornstraße 3, 10785 Berlin
Postal Address: 10785 Berlin
Phone: (004930) 25 54-32 82
Fax: (004930) 25 54-31 87
Mobile: (0170) 167 26 48
E-Mail: jens.hartwig@t-systems.de
Internet: http://www.t-systems.de
> -----Ursprüngliche Nachricht-----
> Von: Nicolas [mailto:bnk@medialife.net]
> Gesendet: Mittwoch, 30. Mai 2001 20:23
> An: pgsql-sql@postgresql.org
> Betreff: [SQL] Distinct Values
>
>
> Hi,
>
> I'm trying to retreive DISTINCT Values from a two colomn table called
> "Books". The colomns are named "Author" and "URL".
> DISTINCT values should be retieved from the "Author" Colomn ,
> and then I
> should be able to retrieve the corresponding URL.
> How do I build the SQL Query ?
> I tried to use: "SELECT DISTINCT Author FROM Books" But this
> does not allow
> me to fetch the URL value on the other colomn.
>
> Thanks
> Nicolas
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: Distinct Values
2001-06-06 06:43 RE: Distinct Values Hartwig Jens <Jens.Hartwig@t-systems.de>
@ 2001-06-06 15:23 ` KuroiNeko <evpopkov@carrier.kiev.ua>
0 siblings, 0 replies; 6+ messages in thread
From: KuroiNeko @ 2001-06-06 15:23 UTC (permalink / raw)
To: pgsql-sql
> Author URL
> ------- --------
> A http://www.xyz http://www.abc .com
> - -
> B http://www.def http://www.ijk http://www.rst .com
> - -
>
> If that is what you want, you cannot manage it.
Sure, in no way this is possible with SQL (in terms of relational DBMS).
However, many people find dup'ed values on the `left pane' annoying and
that's why report tools exist :) Actually, getting rid of dup's is fairly
simple in this exact case if query output is run through a text processing
script in PERL or awk.
--
ÌĤ¯Ç¤ÏÁͤòÊá¤é¤Ì
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2001-06-06 15:23 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-05-30 18:22 Distinct Values Nicolas <bnk@medialife.net>
2001-06-05 09:18 ` Hans-Jürgen Schönig <hs@cybertec.at>
2001-06-05 09:20 ` Hans-Jürgen Schönig <hs@cybertec.at>
2001-06-05 21:14 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2001-06-06 06:43 RE: Distinct Values Hartwig Jens <Jens.Hartwig@t-systems.de>
2001-06-06 15:23 ` KuroiNeko <evpopkov@carrier.kiev.ua>
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