Re: select distinct() question

Date sent: Fri, 18 Apr 2003 12:49:26 +0900
From: Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp>
To: pgsql-novice(at)postgresql(dot)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(at)postgresql(dot)org)