On Sep 25, 2011, at 12:37, "R. Smith" wrote:
> On Sat, Sep 17, 2011 at 2:56 PM, David Johnston wrote:
>> On Sep 17, 2011, at 9:32, "R. Smith" wrote:
>>
>>
>> What I want to do is do a query joining table A with B and sorting
>> firstly on a field in Table A then on several fields in Table B.
>
On Sat, Sep 17, 2011 at 2:56 PM, David Johnston wrote:
> On Sep 17, 2011, at 9:32, "R. Smith" wrote:
>
>
> What I want to do is do a query joining table A with B and sorting
> firstly on a field in Table A then on several fields in Table B.
>
>
> SELECT a.gdn_gdn, a.gdn_custref, a.gdn_date, a.gdn
On Sep 17, 2011, at 9:32, "R. Smith" wrote:
>
> What I want to do is do a query joining table A with B and sorting
> firstly on a field in Table A then on several fields in Table B.
>
>
> SELECT a.gdn_gdn, a.gdn_custref, a.gdn_date, a.gdn_address_name,
> a.gdn_method, b.gdn_stockref, b.gdn_row
Greetings folks,
This is a follow up to my initial message some time ago. Now I have
got all the details together.
I have two tables.
Table A - Which contains one row per entry
Table B - Which contains multiple rows per entry
Table B relates to Table A by a field say called ID. Table A in this
On 23/08/11 01:27, Enzen user wrote:
Hi
I have to rearrange the months according to the fiscal year i.e from April
to march and use the same in the order by clause of a query.
I have written the following postgresql function for the same, but to_number
is returning an error.
Can you please tell
Hello
2011/8/22 Enzen user :
> Hi
> I have to rearrange the months according to the fiscal year i.e from April
> to march and use the same in the order by clause of a query.
> I have written the following postgresql function for the same, but to_number
> is returning an error.
> Can you please te
Hi
I have to rearrange the months according to the fiscal year i.e from April
to march and use the same in the order by clause of a query.
I have written the following postgresql function for the same, but to_number
is returning an error.
Can you please tell me where i'm going wrong?
Instead of t
Of Ozer, Pam
Sent: Wednesday, May 18, 2011 3:22 PM
To: Tom Lane
Cc: Samuel Gendler; em...@encs.concordia.ca; pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue
Is there anywhere that gives you all the available collations and their
definitions? I found with the C collation it now sorts the
: Tuesday, May 10, 2011 9:47 AM
To: Ozer, Pam
Cc: Samuel Gendler; em...@encs.concordia.ca; pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue
"Ozer, Pam" writes:
> Isn't this the English standard for collation? Or is this a non-c
> locale as mentioned below? Is the
SELECT
A.ID,A.FIELD1,A.FIELD2, B.FIELD1,B.FIELD2, B.FIELD1,B.FIELD2, B.FIELD1,B.FIELD2
FROM a
INNER JOIN B
ON a.id = b.a_id
ORDER BY a.field2 ASC, b.field1 ASC ;
- Reply message -
From: "R. Smith"
Date: Fri, May 13, 2011 12:00 pm
Subject: [SQL] Sorting data based fie
Can you provide An example?
Best,
Oliveiros
Enviado via iPhone
Em 13/05/2011, às 04:00 PM, "R. Smith" escreveu:
> Hi,
>
> I am looking for a way to sort data returned from two tables with the
> first sort based on a field from table A and the secord sort based on
> the results of the first so
Hi,
I am looking for a way to sort data returned from two tables with the
first sort based on a field from table A and the secord sort based on
the results of the first sort but the sort field is from table B.
While I can sort on either fields from either table, I cannot get it
to work on both. I
On Tue, May 10, 2011 at 11:45 AM, Samuel Gendler
wrote:
>
>
> On Tue, May 10, 2011 at 9:47 AM, Tom Lane wrote:
>>
>> "Ozer, Pam" writes:
>> > Isn't this the English standard for collation? Or is this a non-c
>> > locale as mentioned below? Is there anyway around this?
>>
>> > LC_COLLATE
On Tue, May 10, 2011 at 9:47 AM, Tom Lane wrote:
> "Ozer, Pam" writes:
> > Isn't this the English standard for collation? Or is this a non-c
> > locale as mentioned below? Is there anyway around this?
>
> >LC_COLLATE = 'en_US.utf8'
>
> en_US is probably using somebody's idea of "dictio
: pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue
That works. Why?
-Original Message-
From: Emi Lu [mailto:em...@encs.concordia.ca]
Sent: Monday, May 09, 2011 12:38 PM
To: Ozer, Pam
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue
> I have the following query
>
&g
"Ozer, Pam" writes:
> Isn't this the English standard for collation? Or is this a non-c
> locale as mentioned below? Is there anyway around this?
>LC_COLLATE = 'en_US.utf8'
en_US is probably using somebody's idea of "dictionary order", which
I believe includes ignoring spaces
7;; Samuel Gendler
Cc: em...@encs.concordia.ca; pgsql-sql@postgresql.org
Subject: RE: [SQL] Sorting Issue
The collate setting is
LC_COLLATE = 'English_United States.1252'
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Monday, May 09, 201
I was wrong it is
LC_COLLATE = 'en_US.utf8'
-Original Message-
From: Ozer, Pam
Sent: Monday, May 09, 2011 3:13 PM
To: 'Tom Lane'; Samuel Gendler
Cc: em...@encs.concordia.ca; pgsql-sql@postgresql.org
Subject: RE: [SQL] Sorting Issue
Th
The collate setting is
LC_COLLATE = 'English_United States.1252'
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Monday, May 09, 2011 2:29 PM
To: Samuel Gendler
Cc: em...@encs.concordia.ca; Ozer, Pam; pgsql-sql@postgresql.org
Subject: Re: [SQL] Sor
Samuel Gendler writes:
> It's not at all clear why they are not coming out of the db in
> alphabetically sorted order when the query includes "order by
> VehicleTrimAbbreviated asc"
Usually the thing to ask at this point is "what's the database's
LC_COLLATE setting"? Non-C locales often have tr
On Mon, May 9, 2011 at 1:38 PM, Emi Lu wrote:
> Hi Pam,
>
>
> >> Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId
> >> from VehicleTrimAbbreviated
> >> Where vehicleTrimAbbreviated like 'CX%'
> >> order by
> >>
> >> split_part(VehicleTrimAbbreviated, ' ', 1) asc,
> >> split_part(VehicleTri
:em...@encs.concordia.ca]
Sent: Monday, May 09, 2011 12:52 PM
To: Ozer, Pam
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue
That works. Why?
http://www.postgresql.org/docs/current/static/functions-string.html
split_part(string text, delimiter text, field int) textSpli
Ok but why doesn't the other way work? I can't use the function in my
query. It is dynamically created.
-Original Message-
From: Emi Lu [mailto:em...@encs.concordia.ca]
Sent: Monday, May 09, 2011 12:52 PM
To: Ozer, Pam
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Sor
Original Message-
From: Emi Lu [mailto:em...@encs.concordia.ca]
Sent: Monday, May 09, 2011 12:38 PM
To: Ozer, Pam
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue
I have the following query
Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId
from VehicleTr
That works. Why?
-Original Message-
From: Emi Lu [mailto:em...@encs.concordia.ca]
Sent: Monday, May 09, 2011 12:38 PM
To: Ozer, Pam
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Sorting Issue
> I have the following query
>
> Select VehicleTrimAbbreviated, VehicleTrimAbbr
I have the following query
Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId
from VehicleTrimAbbreviated
Where vehicleTrimAbbreviated like 'CX%'
order by VehicleTrimAbbreviated asc
Results:
532;"CX Hatchback"
536;"CXL Minivan"
3255;"CXL Premium Sedan"
537;"CXL Sedan"
538;"CXL Sport
I have the following query
Select VehicleTrimAbbreviated, VehicleTrimAbbreviatedId
from VehicleTrimAbbreviated
Where vehicleTrimAbbreviated like 'CX%'
order by VehicleTrimAbbreviated asc
Results:
532;"CX Hatchback"
536;"CXL Minivan"
3255;"CXL Premium Sedan"
537;"CXL Sedan"
538;"CXL
Am 01.11.2010 13:15, schrieb Brian Sherwood:
I am trying to sort router interface names.
The problem is that I am doing a text sort and need to do a numerical sort.
What I get instead is the following text ordering:
GigabitEthernet1/0/1| 1/0/1 | {1,0,1}
GigabitEthernet1/0/10 | 1/
2010/11/1 Brian Sherwood :
> I am trying to sort router interface names.
> The problem is that I am doing a text sort and need to do a numerical sort.
>
> I want the interfaces to be in numerical order:
>
> GigabitEthernet1/0/1 | 1/0/1 | {1,0,1}
> GigabitEthernet1/0/2 | 1/0/2 | {1,0
I am trying to sort router interface names.
The problem is that I am doing a text sort and need to do a numerical sort.
I want the interfaces to be in numerical order:
GigabitEthernet1/0/1 | 1/0/1 | {1,0,1}
GigabitEthernet1/0/2 | 1/0/2 | {1,0,2}
GigabitEthernet1/0/3 | 1/0/3
On Tue, Sep 12, 2006 at 04:37:55PM -0400, Tom Lane wrote:
> Note that if you need to GROUP in the outer query, it's best to sort the
> inner query's output first by the outer query's grouping:
...
Great - this works fine. Thanks also to the other people who replied.
Steven.
--
w: http://www.cl.
Steven Murdoch escreveu:
I would like to concatenate sorted strings in an aggregate function. I
found a way to do it without sorting[1], but not with.
Here is an example of a setup and what I could like to achieve. Does
anyone have suggestions on what is the best way to get the desired
result?
Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Tue, Sep 12, 2006 at 04:46:28PM +0100, Steven Murdoch wrote:
>> Here is an example of a setup and what I could like to achieve. Does
>> anyone have suggestions on what is the best way to get the desired
>> result?
> Use the aggregate over an ordered su
On Sep 12 04:46, Steven Murdoch wrote:
> I would like to concatenate sorted strings in an aggregate function. I
> found a way to do it without sorting[1], but not with.
If the array elements will be made of integers, then you can use sort()
procedure comes with intarray contrib module. For instanc
On Tue, Sep 12, 2006 at 04:46:28PM +0100, Steven Murdoch wrote:
> Here is an example of a setup and what I could like to achieve. Does
> anyone have suggestions on what is the best way to get the desired
> result?
Use the aggregate over an ordered subquery:
SELECT name, trim(concat(code || ' '))
I would like to concatenate sorted strings in an aggregate function. I
found a way to do it without sorting[1], but not with.
Here is an example of a setup and what I could like to achieve. Does
anyone have suggestions on what is the best way to get the desired
result?
Thanks,
Steven.
CREATE TAB
People from brazilian postgresql list sent me another way to sort the
column contents. The way is the aggregate accumulate values and after
run an array sort function. It worked fine. I think the subquery in
function f_select_array does not decrease performance. Follow the
code:
--from http://arc
Thanks a lot the explanation.
I tested all cases and I noticed that reordering the source table (city)
not works on all cases, so I think Postgresql perform different internal
sort to optimize some query's.
I noticed this in other query I performed:
select s.ano,
s.semestre,
It works fine. But I wouldn't like using subselect's, then if somebody else
knows about an operator or something like that to put on the aggregator,
please tell me.
I think the nature of the f_concat makes it difficult to sort, since it
simply adds the next value, so if the source table gives
It works fine. But I wouldn't like using subselect's, then if somebody else
knows about an operator or something like that to put on the
aggregator, please tell me.
Volkan YAZICI escreveu:
On May 02 06:00, Everton Luís Berz wrote:
Is it possible to sort the content of an aggregate text co
On Wed, May 03, 2006 at 00:13:40 +0300,
Volkan YAZICI <[EMAIL PROTECTED]> wrote:
> On May 02 06:00, Everton Luís Berz wrote:
> > Is it possible to sort the content of an aggregate text column?
> >
> > Query:
> > select s.name, ag_concat(c.name) from state s
> > inner join city c on (c.idstate =
On May 02 06:00, Everton Luís Berz wrote:
> Is it possible to sort the content of an aggregate text column?
>
> Query:
> select s.name, ag_concat(c.name) from state s
> inner join city c on (c.idstate = s.idstate)
> group by s.name
> order by s.name;
IMHO, you can receive results ordered by using
Is it possible to sort the content of an aggregate text column?
Query:
select s.name, ag_concat(c.name) from state s
inner join city c on (c.idstate = s.idstate)
group by s.name
order by s.name;
Result:
name | ag_concat
---+---
RS| Porto Alegre, Gramado
SP
ssage -
From: "Joseph Shraibman"
To:
Sent: Wednesday, January 25, 2006 2:23 AM
Subject: [SQL] sorting by day of the week
p8:owl=>SELECT to_char( logtime, 'Dy'),count(*) FROM logtab WHERE
date_trunc('day', logtime) > current_date + '7 day ago&
Nevermind, I figured out that I just needed to do it like this:
SELECT to_char( logtime, 'Dy'),count(*),to_char( logtime, 'D') FROM
sclog WHERE date_trunc('day', logtime) > current_date + '7 day
ago'::interval group by to_char( logtime, 'Dy'),to_char( logtime, 'D')
ORDER BY to_char( logtime, '
p8:owl=>SELECT to_char( logtime, 'Dy'),count(*) FROM logtab WHERE
date_trunc('day', logtime) > current_date + '7 day ago'::interval group
by to_char( logtime, 'Dy') ORDER BY to_char( logtime, 'Dy') DESC;
to_char | count
-+---
Wed | 1447
Tue | 618
Thu | 1161
Sun
Pop text in front of that first value and it works:
template1=# select text '##107990'
template1=# as "sortfield"
template1-# union
template1-# select '###17990'
template1-# order by sortfield;
sortfield
---
On Mon, 12 Jul 2004, Ruggero wrote:
> Hi all,
> I have a problem sorting varchar fields.
> I will explain the problem with a simple example:
>
> this query
>select '##10' as sortfield
>union
>select '###1' as sortfield
>order by sortfield
> produces this correct output:
>'###1'
Hi all,
I have a problem sorting varchar fields.
I will explain the problem with a simple example:
this query
select '##10' as sortfield
union
select '###1' as sortfield
order by sortfield
produces this correct output:
'###1'
'##10'
but this one
select '##10--
"David Witham" <[EMAIL PROTECTED]> writes:
> So I see that there is the extra sort above the sub-query that
> wouldn't be there using 7.4. Are you saying that the sort by survey
> after the sort by survey,question would potentially reorder the
> records initially sorted by survey,question?
Exactly
ese rows even though they don't need to
be?
Regards,
David
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Tuesday, 23 March 2004 16:17
To: David Witham
Cc: [EMAIL PROTECTED]
Subject: Re: [SQL] Sorting an aggregated column
"David Witham" <[EMAIL PROTECTED]
"David Witham" <[EMAIL PROTECTED]> writes:
> This output is correct in this case but there is no guarantee that the
> answers will come out in "question" order. I can't see how to
> incorporate sorting by the "question" column using this approach.
As of PG 7.4 you can reliably use a sorted sub-sel
Hi all,
I ran these commands:
create temp table dw_survey(survey int,question int,answer_bool boolean,answer_int
int,answer_char varchar);
insert into dw_survey values(1,1,'t',null,null);
insert into dw_survey values(1,2,'f',null,null);
insert into dw_survey values(1,3,'t',null,null);
insert int
I think what you actually want is natural sorting.
Ries
> -Oorspronkelijk bericht-
> Van: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Jean-Luc Lachance
> Verzonden: woensdag 15 oktober 2003 17:43
> Aan: George A.J
> CC: [EMAIL PROTECTED]
> Onderwerp: Re:
You are obviously not using C locale.
If you can't change it for some reason, you can use:
select * from accounts order by int4( trim( acno, '#'));
JLL
"George A.J" wrote:
>
> hi all,
> i am using postgres 7.3.2 .i am converitng a mssql database to
> postgres.
> now i am facing a strange proble
On Tue, 14 Oct 2003, George A.J wrote:
> hi all,
> i am using postgres 7.3.2 .i am converitng a mssql database to postgres.
> now i am facing a strange problem. sorting based on a varchar field is not working
> as expected. the non alphanumeric characters are not sorting based on the ascii
> valu
hi all,
i am using postgres 7.3.2 .i am converitng a mssql database to postgres.
now i am facing a strange problem. sorting based on a varchar field is not working
as expected. the non alphanumeric characters are not sorting based on the ascii
value of them.
i have the following table structure.
how do you set sorting for indicies and order by? is it set once for
the database, or can it be set per index?
__
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
---(end of broadc
On Tue, 2003-03-04 at 15:13, Stephan Szabo wrote:
> On Tue, 4 Mar 2003, Ian Burrell wrote:
>
> > I am doing a query where I need to sort by a column that may be NULL
> > because it is coming from an OUTER JOIN. I noticed a difference between
> > PostgreSQL and other databases about where NULLs sh
SELECT name FROM test ORDER BY id DESC LIMIT 10;
Take care,
Jason
--- Wei Weng <[EMAIL PROTECTED]> wrote:
> Suppose I have a table
>
> create table test
> (
> id integer,
> name text
> );
>
> And I want to get the names of the largest 10 "id"s.
> How can I do that in
> sql?
Title: RE: [SQL] Sorting and then...
But if you want the largest 10, then you can:
SELECT name FROM ORDER BY id DESC LIMIT 10;
assuming that you mean largest numerically.
Cheers...
MikeA
>> -Original Message-
>> From: Roberto Mello [mailto:[EMAIL PROTECTED]]
On Mon, Apr 09, 2001 at 07:22:52PM -0400, Wei Weng wrote:
> And I want to get the names of the largest 10 "id"s. How can I do that in
> sql?
What do you mean by "largest"? Largest id? "largest" text string? If
it's the id you can do:
select max(id) from ;
-Roberto
--
Suppose I have a table
create table test
(
id integer,
name text
);
And I want to get the names of the largest 10 "id"s. How can I do that in
sql?
Thanks!
---(end of broadcast)---
TIP 2: you can get off all lists at once with t
Hi,
i have a table with some text fields filled with a data like
100,23
235,12
500
200
the same fields somethimes contains the values like
100x100x25
125x125x50
200x80x90
and so on.
the client requires that rows are sorted in ascending order
for the case there are a float values, i
Hi,
I'm use postgresql-7.0.2. It's compiled with unicode support
(./configure --enable-multibyte=UNICODE ...)
I have a table which contains both latin and non-latin letters. All they are in UTF-8
encoding. When
I try to sort the rows
( SELECT * FROM my_table ORDER BY sort_field )
I receive str
65 matches
Mail list logo
