Postgres Pro
Downloads
Home   >   mailing lists

Thread: select distinct() question

select distinct() question

From
Jean-Christian Imbeault
Date:
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

Re: select distinct() question

From
"paul butler"
Date:
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)

Re: select distinct() question

From
Tom Lane
Date:
Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:
> I observed the following for one of my queries:
> TEST=# select distinct(prod_id), date_received from invoice_li;
> ...
> Is this the correct behaviour for a select distinct()?

Yes.  Hint: the parentheses in what you wrote are noise.

Possibly you were looking for DISTINCT ON, which does have
a parenthetical list of things-to-distinctify.

            regards, tom lane