Thanks for the insight.
Pai-Hung
On 4/18/15 1:30 AM, Pai-Hung Chen wrote:
Thanks for the help. So in this case, the performance of ORDER BY will
not be affected at all by whether an index is created on the jsonb
"setting" field?
No. The optimizer is going to first try and satisfy the WHERE clause
with that relevant index. Bec
"pgsql-general@postgresql.org"
Subject: Re: [GENERAL] ORDER BY for jsonb
On 4/17/15 9:53 PM, Pai-Hung Chen wrote:
> Hi,
>
> I am new to PostgreSQL and have a question about the new jsonb type in
> 9.4. Suppose I have a table called "user" that has two columns: (1)
&g
On 4/17/15 9:53 PM, Pai-Hung Chen wrote:
Hi,
I am new to PostgreSQL and have a question about the new jsonb type in
9.4. Suppose I have a table called "user" that has two columns: (1)
"user_id" of type text, also the primary key, (2) "setting" of type
jsonb. With the following query pattern:
SE
>
>
> On Wed, Jan 21, 2015 at 4:09 PM, Bryn Jeffries <
> bryn.jeffr...@sydney.edu.au> wrote:
>
>>
>> Maybe what we need in ODBC libs and the like is a "protected
>> statement" that follows the same construction as a prepared statement but
>> additionally checks catalogs to validate identifiers.
>>
On 01/21/2015 03:09 PM, Bryn Jeffries wrote:
Paul Jungwirth wrote
I'm not sure how to make a prepared statement that lets you name a
column when you execute it. Maybe someone else can chime in if that's
possible.
David J. responded
You cannot. By definition parameters, in this context, are v
On Wed, Jan 21, 2015 at 4:09 PM, Bryn Jeffries
wrote:
> Paul Jungwirth wrote
> > I'm not sure how to make a prepared statement that lets you name a
> > column when you execute it. Maybe someone else can chime in if that's
> > possible.
>
> David J. responded
> > You cannot. By definition paramet
Paul Jungwirth wrote
> I'm not sure how to make a prepared statement that lets you name a
> column when you execute it. Maybe someone else can chime in if that's
> possible.
David J. responded
> You cannot. By definition parameters, in this context, are values - not
> identifiers.
> [...]
> In
Sorry, I can't find any now. It's cropped up in a few forums, in the context of
executing queries from web services. Clearly not significantly enough to show
up in Google...
- Reply message -
From: "Adrian Klaver"
To: "Bryn Jeffries" ,
"pgsql-general@postgresql.org"
Subject: [GENERAL]
Paul Jungwirth wrote
>> In a number of places on the web I've seen it claimed that ordering can
>> be
>> set via prepared statements.
>> ...
>> sandbox=# PREPARE testplan(text) AS
>> SELECT * FROM test ORDER BY $1;
>>
>> But the output is not what one would expect:
>>
>> sandbox=# EXECUTE testplan(
On 01/21/2015 12:51 PM, Bryn Jeffries wrote:
In a number of places on the web I've seen it claimed that ordering can
be set via prepared statements.
Can you give a link to one of those examples?
Many thanks,
Bryn
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mail
> In a number of places on the web I've seen it claimed that ordering can be
> set via prepared statements.
> ...
> sandbox=# PREPARE testplan(text) AS
> SELECT * FROM test ORDER BY $1;
>
> But the output is not what one would expect:
>
> sandbox=# EXECUTE testplan('gender');
> ...
> As opposed to:
Steve Clark wrote:
> It is like the space character and the - in -Letter is ignored.
Yes, that is how the en_US collation is defined. I think the goal
is to make it something like "phone book" ordering. If you still
have a "white pages" book around, look at how a business name with
a hyphen (o
On 08/07/2014 11:36 AM, Kevin Grittner wrote:
Steve Clark wrote:
I am confused by how postgres 8,4..13 is sorting my data.
select * from test order by data;
data
--
-
--
1
11
11F
1F
a
b
C
F
-F
Feneric
Generic
(14 rows)
The fir
Steve Clark wrote:
> I am confused by how postgres 8,4..13 is sorting my data.
> select * from test order by data;
> data
> --
>
> -
> --
> 1
> 11
> 11F
> 1F
> a
> b
> C
> F
> -F
> Feneric
> Generic
> (14 rows)
>
> The first row is a single space, the next r
On Mon, 07 Apr 2014 11:04:23 +0100
"howardn...@selestial.com" wrote:
> Hi,
>
> just as I thought I had postgres mastered :) the ordering of strings is
> causing me some confusion.
>
> Can someone explain how the database orders strings in the ORDER BY command.
>
> My example:
>
> My databas
howardn...@selestial.com wrote:
>> http://www.postgresql.org/docs/current/static/collation.html#AEN33298
>> The ordering depends on the collation. Which collations are available
>> and how they order depends on your operating system. What is your OS
>> and what do you get for SHOW lc_collate; Yours
On 07/04/2014 11:58, Albe Laurenz wrote:
http://www.postgresql.org/docs/current/static/collation.html#AEN33298
The ordering depends on the collation. Which collations are available
and how they order depends on your operating system. What is your OS
and what do you get for SHOW lc_collate; Your
howardn...@selestial.com wrote:
> just as I thought I had postgres mastered :) the ordering of strings is
> causing me some confusion.
>
> Can someone explain how the database orders strings in the ORDER BY command.
>
> My example:
>
> My database is encoding is UTF-8, and default language is e
Nice one. Yep, that works. Cheers..
I'll submit a bug report for this, though I'm on the fence as to wether
this is actually a bug per se.. I would have reasonably expected my
original syntax to have worked (as it does without any ORDER BY)..
On Thu, Jun 20, 2013 at 3:44 PM, Adrian Klaver wrote:
On 06/20/2013 07:05 AM, Tim Kane wrote:
Hi all,
I seem to be having problems constructing a query that combines the use
of distinct, order by, and collate key words.
For instance:
# select distinct(value) from properties order by 1 collate "C";
ERROR: collations are not supported by type int
writes:
> Please consider this plpgsql function:
> = = = = = = = = = =
> CREATE Or Replace FUNCTION fx_order_by ( )
> RETURNS table( last_name text, first_name )
> AS $eofx$
> DECLARE
> --
> BEGIN
> Return Query
> select
> lname, fname
> from
> my_table
> order by
> lname ASC
salah jubeh writes:
> There is something strange with the views definitions, I have "order by"
> statement amended to the views where the view is ordered by all the columns
> in
> the view. Is this a common behavior?
> Ps: I am using postgresql 8.3
If the views use SELECT DISTINCT, PG version
On Tue, Feb 9, 2010 at 11:21, Fredric Fredricson
wrote:
> Scott Marlowe wrote:
>
> On Tue, Feb 9, 2010 at 1:42 AM, Fredric Fredricson
> wrote:
>
>
> I use locale en_US.UTF-8 but why this should affect how leading characters
> in strings are ignored is beyond me.
>
>
> P.s. this page may shed some
Scott Marlowe wrote:
On Tue, Feb 9, 2010 at 1:42 AM, Fredric Fredricson
wrote:
I use locale en_US.UTF-8 but why this should affect how leading characters
in strings are ignored is beyond me.
P.s. this page may shed some light on the subject:
http://en.wikipedia.org/wiki/Collation
OK,
On Tue, Feb 9, 2010 at 1:42 AM, Fredric Fredricson
wrote:
> I use locale en_US.UTF-8 but why this should affect how leading characters
> in strings are ignored is beyond me.
P.s. this page may shed some light on the subject:
http://en.wikipedia.org/wiki/Collation
--
Sent via pgsql-general mail
On Tue, Feb 9, 2010 at 1:42 AM, Fredric Fredricson
wrote:
> Justin Graf wrote:
>
> On 2/8/2010 7:09 PM, Fredric Fredricson wrote:
>
> Hi!
> New to the list with a question that I cannot find the answer to in the
> manual or on the internet but I suspect is trivial. If somebody could point
> me in
Justin Graf wrote:
On 2/8/2010 7:09 PM, Fredric Fredricson wrote:
Hi!
New to the list with a question that I cannot find the answer to in
the manual or on the internet but I suspect is trivial. If somebody
could point me in the correct direction I would be greatful.
This is what I do (conden
On 2/8/2010 7:09 PM, Fredric Fredricson wrote:
> Hi!
> New to the list with a question that I cannot find the answer to in
> the manual or on the internet but I suspect is trivial. If somebody
> could point me in the correct direction I would be greatful.
>
> This is what I do (condensed, of cour
On Tue, Sep 08, 2009 at 06:29:28AM -0700, BlackMage wrote:
>
> I have a question about ordering by date. I have a table with two fields and
> some date
>
> Name(character varying) | Event_Date(timestamp with timezone)
> A |2009-09-10 5:30:00
> B
On Tue, Sep 08, 2009 at 06:29:28AM -0700, BlackMage wrote:
> I want to order by date and then by name, so I want the result A,B,C,D. The
> problem is when I do a 'SELECT * FROM table_name ORDER BY Event_Date, DESC',
> it includes the actual time (HH:MM:SS) so the order comes out B,A,D,C.
>
> So wh
On Tuesday 08 September 2009 6:29:28 am BlackMage wrote:
> I have a question about ordering by date. I have a table with two fields
> and some date
>
> Name(character varying) | Event_Date(timestamp with timezone)
> A |2009-09-10 5:30:00
> B
In response to BlackMage :
>
> I have a question about ordering by date. I have a table with two fields and
> some date
>
> Name(character varying) | Event_Date(timestamp with timezone)
> A |2009-09-10 5:30:00
> B |2009-0
In response to BlackMage :
>
> I have a question about ordering by date. I have a table with two fields and
> some date
>
> Name(character varying) | Event_Date(timestamp with timezone)
> A |2009-09-10 5:30:00
> B |2009-
In response to Anton Marchenkov :
> Hi!
>
> I'm trying to use the order by parameter inside a function, but it is
> ignored. Any ideas why? And how can I sort by external parameters inside
> pgsql function?
You an use dynamic SQL with EXECUTE, for instance:
create or replace function my_order
On Tuesday 2. June 2009, Anton Marchenkov wrote:
>CREATE OR REPLACE FUNCTION "public"."test_order_by" (sort_key varchar)
>RETURNS SETOF "customers"."customers_with_mark_deleted" AS
>$body$
>DECLARE
> rec RECORD;
>BEGIN
> FOR rec IN SELECT * FROM customers.customers_with_mark_deleted c
>
On Tuesday 2. June 2009, Anton Marchenkov wrote:
>Hi!
>
>I'm trying to use the order by parameter inside a function, but it is
>ignored. Any ideas why? And how can I sort by external parameters
> inside pgsql function?
What's the problem with
SELECT * FROM foo(myvar) ORDER BY sort_key ASC
?
If
Il 18-03-2009 21:32, Bryan Herger ha scritto:
All,
I am porting a database from MS SQL Server to Postgres. One of the
tables contains a list of names, which I would like to list
alphabetically. I noticed in the “O” names the following difference:
MSSQL:
O’Daniel
O’Neill
Oliveira
Oliver
While P
Alvaro Herrera writes:
> Bryan Herger wrote:
>> I am porting a database from MS SQL Server to Postgres. One of the
>> tables contains a list of names, which I would like to list
>> alphabetically. I noticed in the "O" names the following difference:
> Note to whoever answers: this is a frequent
Bryan Herger wrote:
> I am porting a database from MS SQL Server to Postgres. One of the
> tables contains a list of names, which I would like to list
> alphabetically. I noticed in the "O" names the following difference:
Note to whoever answers: this is a frequent question, so consider adding
Kevin Kempter wrote on 26.11.2008 20:46:
Hi All;
I'm selecting 3 columns. I want to order the results ascending by col1 and
col2 and then descending by col3
Whats the syntax for this?
ORDER BY col1 ASC, col2 ASC, col3 DESC
this is documented in the manual
--
Sent via pgsql-general maili
Kevin Kempter <[EMAIL PROTECTED]> schrieb:
> Hi All;
>
> I'm selecting 3 columns. I want to order the results ascending by col1 and
> col2 and then descending by col3
>
> Whats the syntax for this?
order by col1 asc, col2 asc, col3 desc;
Andreas
--
Really, I'm not out to destroy Microsoft.
On Wed, Nov 26, 2008 at 7:46 PM, Kevin Kempter
<[EMAIL PROTECTED]>wrote:
> Hi All;
>
> I'm selecting 3 columns. I want to order the results ascending by col1 and
> col2 and then descending by col3
>
> Whats the syntax for this?
>
select * from foo order by a,b asc, c desc;
?
--
GJ
mike stanton napisal 24.10.2008 21:03:
Hello everyone.
We have a simple problem...that we have keys that include blanks and
various other commonly used characters like ",", ";" and "-". For some
reason, the select we have, nothing complicated, ignores these "special"
characters and happily so
"Scott Marlowe" <[EMAIL PROTECTED]> writes:
> But is there a way to force a use of varchar_pattern_ops on a column
> used in an order by?
ORDER BY x USING ~<~("asc" direction)
ORDER BY x USING ~>~("desc" direction)
(assuming those are the right names of the operators, I'm too lazy
to chec
Kevin Reynolds a écrit :
Does postgresql have something similar to the ORDER BY FIELD feature
found in MySQL?
Something like ORDER BY FIELD(ID, 10, 2, 56, 40);
It is listed here: http://dev.mysql.com/doc/refman/5.0/en/sorting-rows.html
I am using the Sphinx Search program, www.sphinxsearch
phazon <[EMAIL PROTECTED]> writes:
> I've "**" before the name in order to display them at first, but the 2
> chars "**" are ignored in the order by.
What's your lc_collate setting? In locales other than "C" this is not
at all unexpected.
regards, tom lane
--
Stefan Schwarzer wrote:
Hi there,
if I order a given year in DESCending ORDER, so that the highest
values (of a given variable) for the countries are displayed at the
top of the list, then actually the NULL values appear as first. Only
below, I find the values ordered correctly.
Is there an
Stefan Schwarzer ha scritto:
From 8.3 beta release notes:
- ORDER BY ... NULLS FIRST/LAST
I think this is what you want right?
Yes, indeed. Sounds great. unfortunately I am on 8.1. And wouldn't
really want to migrate to 8.3 and beta for the moment
order by 1 ?
---
From 8.3 beta release notes:
- ORDER BY ... NULLS FIRST/LAST
I think this is what you want right?
Yes, indeed. Sounds great. unfortunately I am on 8.1. And
wouldn't really want to migrate to 8.3 and beta for the moment
Thanks anyway!
Stef
---(end of broadca
Richard Huxton escribió:
Stefan Schwarzer wrote:
Hi there,
if I order a given year in DESCending ORDER, so that the highest
values (of a given variable) for the countries are displayed at the
top of the list, then actually the NULL values appear as first.
Only below, I find the values ordered
Stefan Schwarzer wrote:
Hi there,
if I order a given year in DESCending ORDER, so that the highest
values (of a given variable) for the countries are displayed at the
top of the list, then actually the NULL values appear as first. Only
below, I find the values ordered correctly.
Is there any
Hi there,
if I order a given year in DESCending ORDER, so that the highest
values (of a given variable) for the countries are displayed at
the top of the list, then actually the NULL values appear as
first. Only below, I find the values ordered correctly.
Is there any way to
a) make the
Stefan Schwarzer wrote:
Hi there,
if I order a given year in DESCending ORDER, so that the highest values
(of a given variable) for the countries are displayed at the top of the
list, then actually the NULL values appear as first. Only below, I find
the values ordered correctly.
Is there an
On Jun 21, 2007, at 8:08 PM, brian wrote:
Michael Glaesemann wrote:
On Jun 21, 2007, at 17:35 , brian wrote:
I have a lookup table with a bunch of disciplines:
To answer your ordering question first:
SELECT id, name
FROM discipline
ORDER BY name = 'other'
, name;
id |name
+--
Michael Glaesemann wrote:
On Jun 21, 2007, at 17:35 , brian wrote:
I have a lookup table with a bunch of disciplines:
To answer your ordering question first:
SELECT id, name
FROM discipline
ORDER BY name = 'other'
, name;
id |name
+-
8 | community
4 |
Josh Tolley wrote:
It seems to me you could replace it all with one query, something like
this:
SELECT discipline, COUNT(1) FROM showcase WHERE EXISTS (SELECT * FROM
showcase_item WHERE showcase_id = showcase.id LIMIT 1) GROUP BY
discipline ORDER BY (discipline != 'other'), discipline;
disci
On Jun 21, 2007, at 17:35 , brian wrote:
I have a lookup table with a bunch of disciplines:
To answer your ordering question first:
SELECT id, name
FROM discipline
ORDER BY name = 'other'
, name;
id |name
+-
8 | community
4 | dance
5 | film and telev
On 6/21/07, brian <[EMAIL PROTECTED]> wrote:
I have a lookup table with a bunch of disciplines:
# SELECT id, name FROM discipline;
id |name
+-
1 | writing
2 | visual arts
3 | music
4 | dance
5 | film and television
6 | theatre
7 | media arts
On Wed, 28 Mar 2007, Carlos H. Reimer wrote:
> Hi,
>
> We have a PostgreSQL 8.0.6 cluster configured with lc_collate=pt_BR.UTF-8
> and when we run the following SELECT:
> SELECT substr(nomerazao,1,4),
> ascii(substr(nomerazao,1,1)),
> ascii(substr(nomerazao,2,1))
> from spunico.unico order by nome
Carlos H. Reimer wrote:
> Are not the lines out of order
No.
> or is it a normal behaviour for a
> server with lc_collate=pt_BR.UTF-8?
Yes.
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
---(end of broadcast)---
TIP 2: Don't 'kill
On 28/03/2007 22:52, Carlos H. Reimer wrote:
SELECT substr(nomerazao,1,4),
ascii(substr(nomerazao,1,1)),
ascii(substr(nomerazao,2,1))
from spunico.unico order by nomerazao;
You need to add aliases to the returned column by which you'd like to
order the result: your query is ordering the rows
Alvaro Herrera wrote:
> Bruce Momjian wrote:
>
> > Here the column result is an expression, and you reference that.
> > Updated wording:
> >
> > test=> select * from test union select * from test order by x is null;
> > ERROR: A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or
> > f
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Where are the style guidelines? I looked before but can't find them.
http://developer.postgresql.org/pgdocs/postgres/error-style-guide.html
regards, tom lane
---(end of broadcast)
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Updated wording:
>
> > test=> select * from test union select * from test order by x is null;
> > ERROR: A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or
> > functions, only result column names
>
> This does not meet t
Bruce Momjian wrote:
> Here the column result is an expression, and you reference that.
> Updated wording:
>
> test=> select * from test union select * from test order by x is null;
> ERROR: A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or
> functions, only result column names
Th
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Updated wording:
> test=> select * from test union select * from test order by x is null;
> ERROR: A UNION/INTERSECT/EXCEPT ORDER BY cannot use expressions or
> functions, only result column names
This does not meet the style guidelines.
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Well, it can be an expression _if_ the expression _matches_ an existing
> > UNION column.
>
> You're mistaken. It has to be *an output column name*. Not anything else.
Yea, I was thinking of this:
SELECT *, state IS NULL A
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Well, it can be an expression _if_ the expression _matches_ an existing
> UNION column.
You're mistaken. It has to be *an output column name*. Not anything else.
regards, tom lane
---(end of broadcast)-
Mike Benoit wrote:
> That helps some, but I'm sure it could be even more clear.
>
> The main issue is that you can't order by an expression computed by
> unions, correct? So couldn't the error message by something like:
>
> "ORDER BY on a UNION/INTERSECT/EXCEPT result must match existing result
On Thu, 2007-01-11 at 13:44 -0500, Bruce Momjian wrote:
> Tom Lane wrote:
> > Michael Glaesemann <[EMAIL PROTECTED]> writes:
> > > On Dec 26, 2006, at 18:39 , Mike Benoit wrote:
> > >> ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
> > >> the result columns
> >
> > > Even th
Tom Lane wrote:
> Michael Glaesemann <[EMAIL PROTECTED]> writes:
> > On Dec 26, 2006, at 18:39 , Mike Benoit wrote:
> >> ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
> >> the result columns
>
> > Even though state is a column in both tables, the order by is using
> > an
Michael Glaesemann <[EMAIL PROTECTED]> writes:
> On Dec 26, 2006, at 18:39 , Mike Benoit wrote:
>> ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
>> the result columns
> Even though state is a column in both tables, the order by is using
> an expression, rather than a colu
On Dec 26, 2006, at 18:39 , Mike Benoit wrote:
Fails
-
select * from income_tax_rate_us UNION select * from
income_tax_rate_us
order by state is null;
ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
the result columns
Even though state is a column
Richard Huxton wrote:
peter pilsl wrote:
I just get my mind crossed here:
I do a simple select with order on a text-type. In my opinion entries
with leading spaces should be either first (or maybe last) in the list.
But in my select the whitespace just seems to be ignored:
That'll be down
peter pilsl wrote:
I just get my mind crossed here:
I do a simple select with order on a text-type. In my opinion entries
with leading spaces should be either first (or maybe last) in the list.
But in my select the whitespace just seems to be ignored:
That'll be down to your locale settings
George Pavlov wrote:
>> For larger tables, you may have to resort to a
>> union:
>>
>>select * from foo where name != 'Other' order by name
>>union
>>select * from foo where name = 'Other'
>
> Alas, this suggestion is wrong on two counts: (a) UNION expects a single
> ORDER BY that ap
Alternative options for what they're worth - you'd have to explain to
see how efficient they are
select id, name from (
select lower(name) as sortkey, id, name from table where name != 'Other'
union
select 'z' as sortkey, id, name from table where name = 'Other'
) as t
order by sortkey
> For larger tables, you may have to resort to a
> union:
>
>select * from foo where name != 'Other' order by name
>union
>select * from foo where name = 'Other'
Alas, this suggestion is wrong on two counts: (a) UNION expects a single
ORDER BY that applies to the whole recordset and
On Nov 14, 2006, at 23:03 , MicroUser wrote:
I need sorted result but the way like this:
0 | Anna
3 | Fernando
2 | Link
1 | Other
Record '1 | Other' must by at the end of query result.
It's not apparent from your example that you want something other
than a purely lexicographic sort order
On 14 nov 2006, at 23.03, MicroUser wrote:
I need sorted result but the way like this:
0 | Anna
3 | Fernando
2 | Link
1 | Other
Record '1 | Other' must by at the end of query result.
How I can get it?
Well, maybe not the answer you're looking for, but a rather clean way
to do this would
On Nov 14, 2006, at 10:03 pm, MicroUser wrote:
Hi.
I have a table:
ID | Name
0 | Anna
1 | Other
2 | Link
3 | Fernando
I need sorted result but the way like this:
0 | Anna
3 | Fernando
2 | Link
1 | Other
Record '1 | Other' must by at the end of query result.
How I can get it?
Thx.
I s
Jeff Trout wrote:
This isn't terribly clear in the documentation.
But it seems, in 8.0.3 and 8.1.1 that you cannot use a column alias in
an order by if you are using an expression (ie order by
my_column_alias <> 0 asc). If you use a plain asc/desc order by then
it is fine (ie order by my_col
Howard Cole <[EMAIL PROTECTED]> writes:
> I have a query which presents results sorted using the SQL "ORDER BY...
> LIMIT". Now my question is... if the column that the order refers to
> has some repeated data, will the order of results always be the same?
No. You need to add more columns to
On Thu, Sep 22, 2005 at 02:19:00PM +0100, Howard Cole wrote:
> I have a query which presents results sorted using the SQL "ORDER BY...
> LIMIT". Now my question is... if the column that the order refers to
> has some repeated data, will the order of results always be the same?
Not necessarily
Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Thu, Sep 01, 2005 at 05:14:41PM +0200, Martijn van Oosterhout wrote:
>> SELECT aggregate(field) FROM (SELECT field FROM xxx ORDER BY wherever) x;
> I've occasionally relied on this but I've never been completely
> comfortable with it. Is there any gua
On Thu, Sep 01, 2005 at 05:14:41PM +0200, Martijn van Oosterhout wrote:
> On Thu, Sep 01, 2005 at 10:04:12AM +0100, Charlotte Pollock wrote:
> > What I want to be able to do is pass and order by field to the aggregate so
> > I can be certain I get the list of strings in the correct order.
>
> Orde
Charlotte Pollock <[EMAIL PROTECTED]> writes:
> I'm trying to create a aggregate function similar 9but not identical) to
> mysql's group_concat.
> What I want to be able to do is pass and order by field to the aggregate so
> I can be certain I get the list of strings in the correct order.
The way
On Thu, Sep 01, 2005 at 10:04:12AM +0100, Charlotte Pollock wrote:
> Hi All
>
> I'm trying to create a aggregate function similar 9but not identical) to
> mysql's group_concat.
>
> What I want to be able to do is pass and order by field to the aggregate so
> I can be certain I get the list of str
You're also free to set sort_mem (7.4.x) or work_mem (8.0.x) on a per
session basis, so you could try experimenting with raising the value
of those settings during sessions in which your query is running.
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open S
On Sun, Aug 21, 2005 at 12:04:01PM +0200, Ben-Nes Yonatan wrote:
> Hi All,
>
> I got a table with about 4.5 millions rows in it which is connected to
> another table with about 60 millions rows which are used as keywords for
> searching.
>
> I succeded to create fast queries on the first table
Hello,
that's what I was looking for.
Thanks to all for advices,
with best regards,
Julian Legeny
Tuesday, May 10, 2005, 12:14:38 PM, you wrote:
RS> SELECT * FROM MY_TABLE ORDER BY lower(NAME), NAME
RS> The second NAME is to ensure that AAA comes before aaa, otherwise the order
On Tue, 2005-05-10 at 11:41 +0200, Julian Legeny wrote:
> ...
> But I would like to sort all data as following:
>
>NAME
> ---
>AAA
>aaa
>BBB
>bbb
>CCC
>ccc
> How can I write sql command (or set up ORDER BY options) for selecting that?
how about ORDER BY lower
SELECT * FROM MY_TABLE
ORDER BY LOWER(NAME);
Thanks
Dinesh
Pandey
-Original
Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Julian Legeny
Sent: Tuesday, May 10, 2005 3:12 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] ORDER BY options (how
if you order by upper(name) then it will mix them all together, so you won't
have capital before lowercase, but it will put all the lowercase a before
the uppercase b
"Julian Legeny" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hello,
>
>I have following problem:
>
> I have t
On Tue, 10 May 2005 07:41 pm, Julian Legeny wrote:
> Hello,
>
>I have following problem:
> But I would like to sort all data as following:
>
>NAME
> ---
>AAA
>aaa
>BBB
>bbb
>CCC
>ccc
>
>
> How can I write sql command (or set up ORDER BY options) for sele
Thanks Tom it worked.
rgds
Antony Paul
On Mon, 10 Jan 2005 08:39:50 -0500, Tom Lane <[EMAIL PROTECTED]> wrote:
> Richard Huxton writes:
> > Antony Paul wrote:
> >> I need to use ORDER BY clause in a UNION query and the Order BY
> >> columns are not included in the SELECT statement. I tried like
Richard Huxton writes:
> Antony Paul wrote:
>> I need to use ORDER BY clause in a UNION query and the Order BY
>> columns are not included in the SELECT statement. I tried like this
>>
>> (select from a) UNION (select . from b) order by a.ename;
>>
>> It says that
>> ERROR: Attribute
Try
select a.col1 as ename from a
union
select b.othercolumn as ename from b
order by ename
Give the columns you want to order on the same name using the "as XXX"
syntax, and remove the "a." prefix from the order statement.
John Sidney-Woollett
Antony Paul wrote:
Hi,
I need to use ORDER BY cl
Antony Paul wrote:
Hi,
I need to use ORDER BY clause in a UNION query and the Order BY
columns are not included in the SELECT statement. I tried like this
(select from a) UNION (select . from b) order by a.ename;
It says that
ERROR: Attribute "ename" not found
How to do this.
The "o
1 - 100 of 120 matches
Mail list logo
