pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [SQL] ORDER BY
18+ messages / 9 participants
[nested] [flat]
* Re: [SQL] ORDER BY
@ 1999-01-29 17:03 David Hartwig <daveh@insightdist.com>
0 siblings, 0 replies; 18+ messages in thread
From: David Hartwig @ 1999-01-29 17:03 UTC (permalink / raw)
To: áÎ/Ò:= îÏ×<novikov@webclub.ru>Ï× <novikov@webclub.ru>; +Cc: pgsql-sql
SELECT name FROM foo ORDER BY lower(name);
"áÎ/Ò:= îÏ×Ï×" wrote:
> Stupid question - can ORDER BY be case insensitive?
>
> Andrey
^ permalink raw reply [nested|flat] 18+ messages in thread
* Order by
@ 1999-06-02 00:48 Michael Yeung <michaely@alphanet.net.au>
0 siblings, 2 replies; 18+ messages in thread
From: Michael Yeung @ 1999-06-02 00:48 UTC (permalink / raw)
To: pgsql-sql
Hi all,
Is anyone know that if we allow order-by clause in the view structure.
If not, how can we take the ordering of the UNION ALL in 2 different bags ?
Thanks!!
MICHAEL
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] Order by
@ 1999-06-02 08:36 Jan Wieck <wieck@debis.com>
parent: Michael Yeung <michaely@alphanet.net.au>
1 sibling, 0 replies; 18+ messages in thread
From: wieck@debis.com @ 1999-06-02 08:36 UTC (permalink / raw)
To: michaely@alphanet.net.au; +Cc: pgsql-sql
>
> Hi all,
>
> Is anyone know that if we allow order-by clause in the view structure.
> If not, how can we take the ordering of the UNION ALL in 2 different bags ?
ORDER BY (along with many other things on views) is on my
personal TODO. I hope to get it working in v6.6.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] Order by
@ 1999-06-02 09:34 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Michael Yeung <michaely@alphanet.net.au>
1 sibling, 2 replies; 18+ messages in thread
From: Herouth Maoz @ 1999-06-02 09:34 UTC (permalink / raw)
To: Michael Yeung <michaely@alphanet.net.au>; pgsql-sql
At 03:48 +0300 on 02/06/1999, Michael Yeung wrote:
> Hi all,
>
> Is anyone know that if we allow order-by clause in the view structure.
> If not, how can we take the ordering of the UNION ALL in 2 different bags ?
Correct me if I'm wrong, but ORDER BY shoud not be part of a view, in the
same way it is not a part of a table. A table is defined as an unordered
collection of tuples. In any given query, you can define the order of the
tuples returned. But the table itself is not ordered.
A view should look to the user just like a table. The order of the tuples
is part of the query, not part of the table, and not part of the view.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] Order by
@ 1999-06-02 10:45 Jan Wieck <wieck@debis.com>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 0 replies; 18+ messages in thread
From: wieck@debis.com @ 1999-06-02 10:45 UTC (permalink / raw)
To: herouth@oumail.openu.ac.il; +Cc: michaely@alphanet.net.au; pgsql-sql
Herouth Maoz wrote:
>
> At 03:48 +0300 on 02/06/1999, Michael Yeung wrote:
>
>
> > Hi all,
> >
> > Is anyone know that if we allow order-by clause in the view structure.
> > If not, how can we take the ordering of the UNION ALL in 2 different bags ?
>
> Correct me if I'm wrong, but ORDER BY shoud not be part of a view, in the
> same way it is not a part of a table. A table is defined as an unordered
> collection of tuples. In any given query, you can define the order of the
> tuples returned. But the table itself is not ordered.
>
> A view should look to the user just like a table. The order of the tuples
> is part of the query, not part of the table, and not part of the view.
That's all right and I'm not going to allow this just for the
ORDER BY. It will be enabled as kind of a side effect.
Because view's can have a GROUP BY clause, the
rewriter/planner must be able to produce plans where
different left-/righttrees have their own grouping. Grouping
requires a sort and thus sorting of subplans must be
supported.
Why should we deny ORDER BY on views explicitly?
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] Order by
@ 1999-06-02 14:06 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 2 replies; 18+ messages in thread
From: Herouth Maoz @ 1999-06-02 14:06 UTC (permalink / raw)
To: jwieck@debis.com; +Cc: pgsql-sql
At 13:45 +0300 on 02/06/1999, Jan Wieck wrote:
> That's all right and I'm not going to allow this just for the
> ORDER BY. It will be enabled as kind of a side effect.
> Because view's can have a GROUP BY clause, the
> rewriter/planner must be able to produce plans where
> different left-/righttrees have their own grouping. Grouping
> requires a sort and thus sorting of subplans must be
> supported.
>
> Why should we deny ORDER BY on views explicitly?
The SQL92 standard, if I read it right, defines the view by a "query
expression", which in its turn relies on "query specification", which in
turn relies on "table expression", which does not include ORDER BY.
In short, ORDER BY is not part of the query syntax used in the definition
of views, because order is meaningless in views.
The basic idea is that we should get the same results if we use standard
SQL whether we run on PostgreSQL or on Oracle. So, one may write an
application which selects the data from the view, and relies on the order
in the view for something (like grouping). The same program won't work in
any other database system, because you simply can't make that assumption
without adding 'ORDER BY' to your actual query.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] Order by
@ 1999-06-02 14:49 Jan Wieck <wieck@debis.com>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 0 replies; 18+ messages in thread
From: wieck@debis.com @ 1999-06-02 14:49 UTC (permalink / raw)
To: herouth@oumail.openu.ac.il; +Cc: jwieck@debis.com; pgsql-sql
Herouth Maoz wrote:
>
> At 13:45 +0300 on 02/06/1999, Jan Wieck wrote:
>
>
> > That's all right and I'm not going to allow this just for the
> > ORDER BY. It will be enabled as kind of a side effect.
> > Because view's can have a GROUP BY clause, the
> > rewriter/planner must be able to produce plans where
> > different left-/righttrees have their own grouping. Grouping
> > requires a sort and thus sorting of subplans must be
> > supported.
> >
> > Why should we deny ORDER BY on views explicitly?
>
> The SQL92 standard, if I read it right, defines the view by a "query
> expression", which in its turn relies on "query specification", which in
> turn relies on "table expression", which does not include ORDER BY.
>
> In short, ORDER BY is not part of the query syntax used in the definition
> of views, because order is meaningless in views.
Right now, the parser accepts ORDER BY in CREATE VIEW because
the grammar includes the full SelectStmt rule for the view's
query expression. The LIMIT, DISTINCT and ORDER BY clauses
are rejected later in rewriteDefine.c.
>
> The basic idea is that we should get the same results if we use standard
> SQL whether we run on PostgreSQL or on Oracle. So, one may write an
> application which selects the data from the view, and relies on the order
> in the view for something (like grouping). The same program won't work in
> any other database system, because you simply can't make that assumption
> without adding 'ORDER BY' to your actual query.
GROUP BY must be possible in a view's query expression.
Without that views with aggregate columns could never work
properly.
If I now read your above statements right, "query expression"
-> "query specification" -> "table expression" also forbid's
GROUP BY, because it cannot occur in a table expression -
right? That means that a fully SQL92 compliant DB cannot
support aggregates in view definitions?
Oh what a perfect world - I'm stressing my brain to find a
way how they could work and the real solution is to forbid
them :-)
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: [SQL] Order by
@ 1999-06-02 15:08 Herouth Maoz <herouth@oumail.openu.ac.il>
parent: Herouth Maoz <herouth@oumail.openu.ac.il>
1 sibling, 0 replies; 18+ messages in thread
From: Herouth Maoz @ 1999-06-02 15:08 UTC (permalink / raw)
To: jwieck@debis.com; +Cc: jwieck@debis.com; pgsql-sql
At 17:49 +0300 on 02/06/1999, Jan Wieck wrote:
> GROUP BY must be possible in a view's query expression.
> Without that views with aggregate columns could never work
> properly.
>
> If I now read your above statements right, "query expression"
> -> "query specification" -> "table expression" also forbid's
> GROUP BY, because it cannot occur in a table expression -
> right? That means that a fully SQL92 compliant DB cannot
> support aggregates in view definitions?
No, "table expression" includes "group by" and "having", because they
define the data itself. They actually say something about the set of
tuples. Here is the "table expression" definition:
<table expression> ::=
<from clause>
[ <where clause> ]
[ <group by clause> ]
[ <having clause> ]
Here is the "query specification":
<query specification> ::=
SELECT [ <set quantifier> ] <select list> <table expression>
<select list> ::=
<asterisk>
| <select sublist> [ { <comma> <select sublist> }... ]
<select sublist> ::=
<derived column>
| <qualifier> <period> <asterisk>
<derived column> ::= <value expression> [ <as clause> ]
<as clause> ::= [ AS ] <column name>
Finally, if you wondered where the syntax allows for unions and such like
for the definition of a view, here it is:
<query expression> ::=
<non-join query expression>
| <joined table>
<non-join query expression> ::=
<non-join query term>
| <query expression> UNION [ ALL ] [ <corresponding spec> ] <query term>
| <query expression> EXCEPT [ ALL ] [ <corresponding spec> ] <query term>
<query term> ::=
<non-join query term>
| <joined table>
<non-join query term> ::=
<non-join query primary>
| <query term> INTERSECT [ ALL ] [ <corresponding spec> ] <query primary>
<query primary> ::=
<non-join query primary>
| <joined table>
<non-join query primary> ::=
<simple table>
| <left paren> <non-join query expression> <right paren>
<simple table> ::=
<query specification>
| <table value constructor>
| <explicit table>
<explicit table> ::= TABLE <table name>
<corresponding spec> ::=
CORRESPONDING [ BY <left paren> <corresponding column list> <right
paren> ]
<corresponding column list> ::= <column name list>
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: order by
@ 2003-03-26 11:29 Tomasz Myrta <jasiek@klaster.net>
parent: Jodi Kanter <jkanter@virginia.edu>
1 sibling, 0 replies; 18+ messages in thread
From: Tomasz Myrta @ 2003-03-26 11:29 UTC (permalink / raw)
To: Jodi Kanter <jkanter@virginia.edu>; pgsql-sql
> Is there a known problem with the SQL order by clause within
> postgres version 7.2.3? I am trying to sort a field which holds file
> names that have some spaces as well non-alphanumeric characters,
> including underscores (see below).
>
> The results of:
> select study_name from study order by study_name;
>
> Study1a
> Study 1b
> Study 1c
> Study1d
>
> The results of sorting in Perl (which uses Linux's underlying sort
> function)
>
> Study 1c
> Study 1b
> Study1a
> Study1d
>
> Is there any way around this difference? Blank spaces should show up
> first in the sort.
I made a test table with varchar field and filled it with your values.
I got the same result as in Perl. I have Postgresql 7.3. I'm not sure what
you really want to get.
template1=# create table test (x varchar);
template1=# insert into test values ('Study1a');
template1=# insert into test values ('Study 1b');
template1=# insert into test values ('Study 1c');
template1=# insert into test values ('Study1d');
template1=# SELECT * from test order by x asc;
x
-----------
Study 1c
Study 1b
Study1a
Study1d
(4 rows)
Regards,
Tomasz Myrta
^ permalink raw reply [nested|flat] 18+ messages in thread
* order by
@ 2003-03-26 17:38 Jodi Kanter <jkanter@virginia.edu>
0 siblings, 1 reply; 18+ messages in thread
From: Jodi Kanter @ 2003-03-26 17:38 UTC (permalink / raw)
To: pgsql-sql
Is there a known problem with the SQL order by clause within postgres version 7.2.3?
I am trying to sort a field which holds file names that have some spaces as well non-alphanumeric characters, including underscores (see below).
The results of:
select study_name from study order by study_name;
Study1a
Study 1b
Study 1c
Study1d
The results of sorting in Perl (which uses Linux's underlying sort function)
Study 1c
Study 1b
Study1a
Study1d
Is there any way around this difference?
Thanks
Jodi
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
^ permalink raw reply [nested|flat] 18+ messages in thread
* order by
@ 2003-03-26 18:12 Jodi Kanter <jkanter@virginia.edu>
0 siblings, 2 replies; 18+ messages in thread
From: Jodi Kanter @ 2003-03-26 18:12 UTC (permalink / raw)
To: pgsql-sql
Is there a known problem with the SQL order by clause within postgres version 7.2.3?
I am trying to sort a field which holds file names that have some spaces as well non-alphanumeric characters, including underscores (see below).
The results of:
select study_name from study order by study_name;
Study1a
Study 1b
Study 1c
Study1d
The results of sorting in Perl (which uses Linux's underlying sort function)
Study 1c
Study 1b
Study1a
Study1d
Is there any way around this difference? Blank spaces should show up first in the sort.
Thanks
Jodi
_______________________________
Jodi L Kanter
BioInformatics Database Administrator
University of Virginia
(434) 924-2846
jkanter@virginia.edu
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: order by
@ 2003-03-26 18:44 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Jodi Kanter <jkanter@virginia.edu>
1 sibling, 0 replies; 18+ messages in thread
From: Stephan Szabo @ 2003-03-26 18:44 UTC (permalink / raw)
To: Jodi Kanter <jkanter@virginia.edu>; +Cc: pgsql-sql
On Wed, 26 Mar 2003, Jodi Kanter wrote:
> Is there a known problem with the SQL order by clause within postgres
> version 7.2.3? I am trying to sort a field which holds file names that
> have some spaces as well non-alphanumeric characters, including
> underscores (see below).
>
> The results of:
> select study_name from study order by study_name;
>
>
> Study1a
> Study 1b
> Study 1c
> Study1d
>
>
> The results of sorting in Perl (which uses Linux's underlying sort function)
>
> Study 1c
> Study 1b
> Study1a
> Study1d
>
> Is there any way around this difference? Blank spaces should show up
> first in the sort.
Are you in "C" locale or something like "en_US"? In the latter, the first
is the "correct" ordering.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: order by
@ 2003-03-27 06:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jodi Kanter <jkanter@virginia.edu>
0 siblings, 1 reply; 18+ messages in thread
From: Tom Lane @ 2003-03-27 06:57 UTC (permalink / raw)
To: Jodi Kanter <jkanter@virginia.edu>; +Cc: pgsql-sql
Jodi Kanter <jkanter@virginia.edu> writes:
> Is there a known problem with the SQL order by clause within postgres versi=
> on 7.2.3?
No ... but it does honor the sorting rules of the locale you initialize
the database in. You might want to re-initdb with LC_ALL set to C.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: order by
@ 2003-03-27 14:23 Jodi Kanter <jkanter@virginia.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 18+ messages in thread
From: Jodi Kanter @ 2003-03-27 14:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
When we originally initialized I set the LC_COLLATE=C. Was this not enough?
We set it in the environment variables for the postgres user.
Thanks
Jodi
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Jodi Kanter" <jkanter@virginia.edu>
Cc: "Postgres SQL List" <pgsql-sql@postgresql.org>
Sent: Thursday, March 27, 2003 1:57 AM
Subject: Re: [SQL] order by
> Jodi Kanter <jkanter@virginia.edu> writes:
> > Is there a known problem with the SQL order by clause within postgres
versi=
> > on 7.2.3?
>
> No ... but it does honor the sorting rules of the locale you initialize
> the database in. You might want to re-initdb with LC_ALL set to C.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: order by
@ 2003-03-27 14:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jodi Kanter <jkanter@virginia.edu>
0 siblings, 2 replies; 18+ messages in thread
From: Tom Lane @ 2003-03-27 14:38 UTC (permalink / raw)
To: Jodi Kanter <jkanter@virginia.edu>; +Cc: pgsql-sql
Jodi Kanter <jkanter@virginia.edu> writes:
> When we originally initialized I set the LC_COLLATE=C. Was this not enough?
Should have been ... but see what pg_controldata says about the actual
setting.
regards, tom lane
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: order by
@ 2003-03-27 14:51 Jodi Kanter <jkanter@virginia.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 18+ messages in thread
From: Jodi Kanter @ 2003-03-27 14:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
Can I look at this via the command line? within psql? or in one of the .conf
files?
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Jodi Kanter" <jkanter@virginia.edu>
Cc: "Postgres SQL List" <pgsql-sql@postgresql.org>
Sent: Thursday, March 27, 2003 9:38 AM
Subject: Re: [SQL] order by
> Jodi Kanter <jkanter@virginia.edu> writes:
> > When we originally initialized I set the LC_COLLATE=C. Was this not
enough?
>
> Should have been ... but see what pg_controldata says about the actual
> setting.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: order by
@ 2003-03-27 14:55 Jodi Kanter <jkanter@virginia.edu>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 18+ messages in thread
From: Jodi Kanter @ 2003-03-27 14:55 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
I figured out how to run it and it says that LC_COLLATE and LC_CTYPE are
equal to en_US. Can someone please outline the steps to correcting this. I
thought we had done it correctly from initialization but apparently not.
If I need to reinitialize now will that mess with my current databases? Will
I have to shut down production, do a pg_dump and restore?
Other than the sort ordering are there any other problems with NOT having
the environment set to "C?"
Thanks
Jodi
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Jodi Kanter" <jkanter@virginia.edu>
Cc: "Postgres SQL List" <pgsql-sql@postgresql.org>
Sent: Thursday, March 27, 2003 9:38 AM
Subject: Re: [SQL] order by
> Jodi Kanter <jkanter@virginia.edu> writes:
> > When we originally initialized I set the LC_COLLATE=C. Was this not
enough?
>
> Should have been ... but see what pg_controldata says about the actual
> setting.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: order by
@ 2003-03-27 15:06 Julian Scarfe <julian.scarfe@ntlworld.com>
parent: Jodi Kanter <jkanter@virginia.edu>
0 siblings, 0 replies; 18+ messages in thread
From: Julian Scarfe @ 2003-03-27 15:06 UTC (permalink / raw)
To: Jodi Kanter <jkanter@virginia.edu>; +Cc: pgsql-sql
On 27/3/03 14:55, "Jodi Kanter" <jkanter@virginia.edu> wrote:
> Other than the sort ordering are there any other problems with NOT having
> the environment set to "C?"
Pattern matching e.g. "where foo ~~ 'bar%'" doesn't use an index on foo
unless the locale is C. This can cause a significant performance hit. For
examples as above you can workaround by defining a substring function for a
functional index, but it's a bit clumsy.
Must get round to doing a fresh initdb one of these days... ;-)
Julian Scarfe
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2003-03-27 15:06 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-01-29 17:03 Re: [SQL] ORDER BY David Hartwig <daveh@insightdist.com>
1999-06-02 00:48 Order by Michael Yeung <michaely@alphanet.net.au>
1999-06-02 08:36 ` Re: [SQL] Order by Jan Wieck <wieck@debis.com>
1999-06-02 09:34 ` Re: [SQL] Order by Herouth Maoz <herouth@oumail.openu.ac.il>
1999-06-02 10:45 ` Re: [SQL] Order by Jan Wieck <wieck@debis.com>
1999-06-02 14:06 ` Re: [SQL] Order by Herouth Maoz <herouth@oumail.openu.ac.il>
1999-06-02 14:49 ` Re: [SQL] Order by Jan Wieck <wieck@debis.com>
1999-06-02 15:08 ` Re: [SQL] Order by Herouth Maoz <herouth@oumail.openu.ac.il>
2003-03-26 17:38 order by Jodi Kanter <jkanter@virginia.edu>
2003-03-27 06:57 ` Re: order by Tom Lane <tgl@sss.pgh.pa.us>
2003-03-27 14:23 ` Re: order by Jodi Kanter <jkanter@virginia.edu>
2003-03-27 14:38 ` Re: order by Tom Lane <tgl@sss.pgh.pa.us>
2003-03-27 14:51 ` Re: order by Jodi Kanter <jkanter@virginia.edu>
2003-03-27 14:55 ` Re: order by Jodi Kanter <jkanter@virginia.edu>
2003-03-27 15:06 ` Re: order by Julian Scarfe <julian.scarfe@ntlworld.com>
2003-03-26 18:12 order by Jodi Kanter <jkanter@virginia.edu>
2003-03-26 11:29 ` Re: order by Tomasz Myrta <jasiek@klaster.net>
2003-03-26 18:44 ` Re: order by Stephan Szabo <sszabo@megazone23.bigpanda.com>
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