pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSELECT DISTINCT question
10+ messages / 4 participants
[nested] [flat]
* SELECT DISTINCT question
@ 1999-07-10 20:16 Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 2 replies; 10+ messages in thread
From: Oleg Bartunov @ 1999-07-10 20:16 UTC (permalink / raw)
To: hackers@postgreSQL.org; +Cc: pgsql-sql
I got a problem with query:
select distinct (date), bytes from access_log;
which works but not as I expect. I thought this query will select
all rows with distinct values of 'date' column, but it get
distinct pairs 'date, bytes' . From documnetation I see
"DISTINCT will eliminate all duplicate rows from the selection.
DISTINCT ON column will eliminate all duplicates in the specified column;
this is equivalent to using GROUP BY column.
ALL will return all candidate rows, including duplicates."
discovery=> select distinct on date,bytes from access_log;
ERROR: parser: parse error at or near ","
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] SELECT DISTINCT question
@ 1999-07-10 21:18 Tom Lane <tgl@sss.pgh.pa.us>
parent: Oleg Bartunov <oleg@sai.msu.su>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 1999-07-10 21:18 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: hackers@postgreSQL.org; pgsql-sql
Oleg Bartunov <oleg@sai.msu.su> writes:
> discovery=> select distinct on date,bytes from access_log;
> ERROR: parser: parse error at or near ","
The syntax for SELECT DISTINCT ON is just as brain-damaged as the
functionality itself: there's no comma after the column name.
You want
select distinct on date date,bytes from access_log;
The reason the functionality is brain-damaged is that there's no way to
know which tuple out of the set of tuples with a given "date" value will
be the one returned.
SELECT DISTINCT ON is not in SQL92, and I think it shouldn't be in
Postgres either...
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Re: [HACKERS] SELECT DISTINCT question
@ 1999-07-11 06:09 Oleg Bartunov <oleg@sai.msu.su>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Oleg Bartunov @ 1999-07-11 06:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: hackers@postgreSQL.org; pgsql-sql
On Sat, 10 Jul 1999, Tom Lane wrote:
> Date: Sat, 10 Jul 1999 17:18:28 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Oleg Bartunov <oleg@sai.msu.su>
> Cc: hackers@postgreSQL.org, pgsql-sql@postgreSQL.org
> Subject: [SQL] Re: [HACKERS] SELECT DISTINCT question
>
> Oleg Bartunov <oleg@sai.msu.su> writes:
> > discovery=> select distinct on date,bytes from access_log;
> > ERROR: parser: parse error at or near ","
>
> The syntax for SELECT DISTINCT ON is just as brain-damaged as the
> functionality itself: there's no comma after the column name.
> You want
>
> select distinct on date date,bytes from access_log;
>
thanks, this works. But why parser complains about such query:
discovery=> select distinct on a.date a.date, a.bytes from access_log a;
ERROR: parser: parse error at or near "."
In this query I could just omit '.', but in more complex query
I probably could need one.
> The reason the functionality is brain-damaged is that there's no way to
> know which tuple out of the set of tuples with a given "date" value will
> be the one returned.
>
> SELECT DISTINCT ON is not in SQL92, and I think it shouldn't be in
> Postgres either...
I'm not an SQL expert, but if it works and this feature is in standard
but only syntax is diffrent, why just not to use standard
select distinct(date), bytes from access_log;
Or I'm missing here ?
Regards,
Oleg
>
> regards, tom lane
>
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Re: [HACKERS] SELECT DISTINCT question
@ 1999-07-11 15:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Oleg Bartunov <oleg@sai.msu.su>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 1999-07-11 15:38 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: hackers@postgreSQL.org; pgsql-sql
Oleg Bartunov <oleg@sai.msu.su> writes:
> thanks, this works. But why parser complains about such query:
> discovery=> select distinct on a.date a.date, a.bytes from access_log a;
> ERROR: parser: parse error at or near "."
Probably the grammar specifies just <column name> and not anything
more complex after DISTINCT ON. It'd be risky to try to accept a
general expression after ON, due to the silly decision to leave out
any terminating punctuation.
>> SELECT DISTINCT ON is not in SQL92, and I think it shouldn't be in
>> Postgres either...
> I'm not an SQL expert, but if it works and this feature is in standard
> but only syntax is diffrent,
No, there is no feature in SQL that allows DISTINCT on a subset of
columns, period. This is not merely a matter of syntax, it's a
fundamental semantic issue.
> why just not to use standard
>
> select distinct(date), bytes from access_log;
>
> Or I'm missing here ?
I don't think that does what you expect it to (hint: the
parentheses are redundant).
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] SELECT DISTINCT question
@ 1999-07-13 20:50 Hannu Krosing <hannu@trust.ee>
parent: Oleg Bartunov <oleg@sai.msu.su>
1 sibling, 1 reply; 10+ messages in thread
From: Hannu Krosing @ 1999-07-13 20:50 UTC (permalink / raw)
To: Oleg Bartunov <oleg@sai.msu.su>; +Cc: hackers@postgreSQL.org; pgsql-sql
Oleg Bartunov wrote:
>
> I got a problem with query:
>
> select distinct (date), bytes from access_log;
>
> which works but not as I expect. I thought this query will select
> all rows with distinct values of 'date' column, but it get
> distinct pairs 'date, bytes' . From documnetation I see
>
> "DISTINCT will eliminate all duplicate rows from the selection.
> DISTINCT ON column will eliminate all duplicates in the specified column;
> this is equivalent to using GROUP BY column.
If it is equivalent to GROUP BY then it should allow only aggregates
in non-distinct columns, like:
select distinct on date date, sum(bytes) from access_log;
If it does not, then it should be files as a bug imho.
-----------------
Hannu
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [HACKERS] SELECT DISTINCT question
@ 1999-07-13 21:31 Tom Lane <tgl@sss.pgh.pa.us>
parent: Hannu Krosing <hannu@trust.ee>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 1999-07-13 21:31 UTC (permalink / raw)
To: Hannu Krosing <hannu@trust.ee>; +Cc: hackers@postgreSQL.org; pgsql-sql
Hannu Krosing <hannu@trust.ee> writes:
>> "DISTINCT will eliminate all duplicate rows from the selection.
>> DISTINCT ON column will eliminate all duplicates in the specified column;
>> this is equivalent to using GROUP BY column."
> If it is equivalent to GROUP BY then it should allow only aggregates
> in non-distinct columns, like:
> select distinct on date date, sum(bytes) from access_log;
> If it does not, then it should be files as a bug imho.
It does not. Whether that is a bug is hard to say, since there is no
standard I know of that says what it *is* supposed to do.
If you look at the select_distinct_on regress test outputs, I bet you
will be even less happy:
QUERY: SELECT DISTINCT ON string4 two, string4, ten
FROM tmp
ORDER BY two using <, string4 using <, ten using <;
two|string4|ten
---+-------+---
0|AAAAxx | 0
0|HHHHxx | 0
0|OOOOxx | 0
0|VVVVxx | 0
1|AAAAxx | 1
1|HHHHxx | 1
1|OOOOxx | 1
1|VVVVxx | 1
(8 rows)
That's not exactly my idea of "distinct" values of string4 ---
but apparently whoever made up the regress test thought it was OK!
Can anyone defend this feature or provide a coherent definition
of what it's supposed to be doing? My urge to rip it out is
growing stronger and stronger...
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Re: [HACKERS] SELECT DISTINCT question
@ 1999-09-23 17:18 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Bruce Momjian @ 1999-09-23 17:18 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Hannu Krosing <hannu@trust.ee>; hackers@postgreSQL.org; pgsql-sql
Tom, any status on this DISTINCT ON ripout?
> Hannu Krosing <hannu@trust.ee> writes:
> >> "DISTINCT will eliminate all duplicate rows from the selection.
> >> DISTINCT ON column will eliminate all duplicates in the specified column;
> >> this is equivalent to using GROUP BY column."
>
> > If it is equivalent to GROUP BY then it should allow only aggregates
> > in non-distinct columns, like:
> > select distinct on date date, sum(bytes) from access_log;
> > If it does not, then it should be files as a bug imho.
>
> It does not. Whether that is a bug is hard to say, since there is no
> standard I know of that says what it *is* supposed to do.
>
> If you look at the select_distinct_on regress test outputs, I bet you
> will be even less happy:
>
> QUERY: SELECT DISTINCT ON string4 two, string4, ten
> FROM tmp
> ORDER BY two using <, string4 using <, ten using <;
> two|string4|ten
> ---+-------+---
> 0|AAAAxx | 0
> 0|HHHHxx | 0
> 0|OOOOxx | 0
> 0|VVVVxx | 0
> 1|AAAAxx | 1
> 1|HHHHxx | 1
> 1|OOOOxx | 1
> 1|VVVVxx | 1
> (8 rows)
>
> That's not exactly my idea of "distinct" values of string4 ---
> but apparently whoever made up the regress test thought it was OK!
>
> Can anyone defend this feature or provide a coherent definition
> of what it's supposed to be doing? My urge to rip it out is
> growing stronger and stronger...
>
> regards, tom lane
>
>
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Re: [HACKERS] SELECT DISTINCT question
@ 1999-09-23 21:27 Tom Lane <tgl@sss.pgh.pa.us>
parent: Bruce Momjian <maillist@candle.pha.pa.us>
0 siblings, 2 replies; 10+ messages in thread
From: Tom Lane @ 1999-09-23 21:27 UTC (permalink / raw)
To: Bruce Momjian <maillist@candle.pha.pa.us>; +Cc: Hannu Krosing <hannu@trust.ee>; hackers@postgreSQL.org; pgsql-sql
Bruce Momjian <maillist@candle.pha.pa.us> writes:
> Tom, any status on this DISTINCT ON ripout?
I haven't done anything about it, if that's what you mean.
I didn't get any indication of whether anyone else agreed with me
(maybe the lack of loud complaints should be taken as consent?)
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Re: [HACKERS] SELECT DISTINCT question
@ 1999-09-23 21:56 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 10+ messages in thread
From: Bruce Momjian @ 1999-09-23 21:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Hannu Krosing <hannu@trust.ee>; hackers@postgreSQL.org; pgsql-sql
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > Tom, any status on this DISTINCT ON ripout?
>
> I haven't done anything about it, if that's what you mean.
>
> I didn't get any indication of whether anyone else agreed with me
> (maybe the lack of loud complaints should be taken as consent?)
I think so.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: [SQL] Re: [HACKERS] SELECT DISTINCT question
@ 1999-09-23 21:58 Bruce Momjian <maillist@candle.pha.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 10+ messages in thread
From: Bruce Momjian @ 1999-09-23 21:58 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Hannu Krosing <hannu@trust.ee>; hackers@postgresql.org; pgsql-sql
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > Tom, any status on this DISTINCT ON ripout?
>
> I haven't done anything about it, if that's what you mean.
>
> I didn't get any indication of whether anyone else agreed with me
> (maybe the lack of loud complaints should be taken as consent?)
I will wrap up the mail messages, and put it on the TODO list.
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~1999-09-23 21:58 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-07-10 20:16 SELECT DISTINCT question Oleg Bartunov <oleg@sai.msu.su>
1999-07-10 21:18 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-11 06:09 ` Oleg Bartunov <oleg@sai.msu.su>
1999-07-11 15:38 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-07-13 20:50 ` Hannu Krosing <hannu@trust.ee>
1999-07-13 21:31 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-09-23 17:18 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-09-23 21:27 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-09-23 21:56 ` Bruce Momjian <maillist@candle.pha.pa.us>
1999-09-23 21:58 ` Bruce Momjian <maillist@candle.pha.pa.us>
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