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

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;

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)
> 

> 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.


--
������������������

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    | dsafsdfcde    | sdfdsf
(2 rows)
   Hans

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