Re: select distinct() question

Date sent:          Fri, 18 Apr 2003 12:49:26 +0900
From:               Jean-Christian Imbeault <jc@mega-bucks.co.jp>
To:                 pgsql-novice@postgresql.org
Subject:            [NOVICE] select distinct() question

As I understand it, distinct applies to all distinct combinations of columns specified.
SQL92 allows for multiple distinct on aggregates only (and this can be quite a tricky
area).
so for a table:

 id  | x
-----+-------
 124 | type1
 124 | type1
 124 | type2
 124 | type2
 124 | type2
 124 | type2
  58 | type3
  58 | type3
  58 | type3
  58 | type2

select distinct (id),x from cv;

 id  | x
-----+------
  58 | type2
  58 | type3
 124 | type1
 124 | type2
 124 | type3

select distinct (id) from cv;

 id
-----
  58
 124

select distinct x from cv;

  x
------
 type2
 type3
 type1

so your result suggests there is at least one of each tuple with values:

prod_id | date_received
---------+---------------
    10994 | 2003-04-02

  prod_id | date_received
   ---------+---------------
    10994 | 2003-04-02

prod_id | date_received
 ---------+---------------
  12445 | 2003-04-08

prod_id | date_received
 ---------+---------------
  12445 |


hope this helps

Paul Butler

> I observed the following for one of my queries:
>
> TEST=# select distinct(prod_id), date_received from invoice_li;
>   prod_id | date_received
> ---------+---------------
>     10994 | 2003-04-02
>     12445 | 2003-04-08
>     12445 |
> (3 rows)
>
>
> Is this the correct behaviour for a select distinct()?
>
> I tried understanding what the SQL standard says but was thoroughly
> confused ;)
>
> Jean-Christian Imbeault
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)