Re: [SQL] CROSS JOIN ordering

2011-05-12 Thread Richard Huxton 

On 12/05/11 00:19, Grzegorz Szpetkowski wrote:

Is there any "proper", standard ordering that I can assume for sure ?
Maybe PostgreSQL 8.4/9.0 versions have strict ordering and older
versions are using mixed ordering depends on something I don't know (I
am just guessing).


Queries do not have any guaranteed order unless you add an ORDER BY 
clause. None of them, no matter how simple.


--
  Richard Huxton
  Archonet Ltd

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] replace " with nothing

2011-05-12 Thread Lew 

Tony Capobianco wrote:

We are converting from Oracle to Postgres.  An Oracle script contains
this line:

  select replace(firstname,'"'), memberid, emailaddress from members;

in an effort to replace the " with nothing.  How can I achieve the same
result with Postgres?

Here's the Postgres error I get:

select replace(firstname,'"'), memberid, emailaddress from members;
ERROR:  function replace(character varying, unknown) does not exist
LINE 1: select replace(firstname,'"'), memberid, emailaddress from m...





replace(string text, from text, to text)

--
Lew
Honi soit qui mal y pense.
http://upload.wikimedia.org/wikipedia/commons/c/cf/Friz.jpg

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] ordering by date for each ID

2011-05-12 Thread Nguyen,Diep T 
Hi all,
I have this table
 id |   date | score_count 
+--+-
 13 | 1999-09-16   |   4
 13 | 2002-06-27   |   4
 13 | 2006-10-25   |   4
 13 | 2010-05-12   |   4
 65 | 2002-07-18   |   3
 65 | 2004-08-05   |   3
 65 | 2007-08-15   |   3
 86 | 2001-05-29   |   5
 86 | 2002-04-04   |   5
 86 | 2006-03-02   |   5
 86 | 2008-02-13   |   5
 86 | 2011-01-19   |   5

Each ID can have different number of score counts.

My goal is to add column "order", which shows the order of the values
in column "date" in descendant order for each property. The expected output
will look like this:

 id |   date   | score_count | order
+--+---+---
 13 | 1999-09-16   |   4| 4
 13 | 2002-06-27   |   4| 3
 13 | 2006-10-25   |   4| 2
 13 | 2010-05-12   |   4| 1
 65 | 2002-07-18   |   3| 3
 65 | 2004-08-05   |   3| 2
 65 | 2007-08-15   |   3| 1
 86 | 2001-05-29   |   5| 5
 86 | 2002-04-04   |   5| 4
 86 | 2006-03-02   |   5| 3
 86 | 2008-02-13   |   5| 2 
 86 | 2011-01-19   |   5| 1

Any help would be appreciated.

Thanks,

Diep
-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] ordering by date for each ID

2011-05-12 Thread Thomas Kellerer 

Nguyen,Diep T wrote on 12.05.2011 03:59:


Each ID can have different number of score counts.

My goal is to add column "order", which shows the order of the values
in column "date" in descendant order for each property. The expected output
will look like this:

  id |   date   | score_count | order
+--+---+---
  13 | 1999-09-16   |   4| 4
  13 | 2002-06-27   |   4| 3
  13 | 2006-10-25   |   4| 2
  13 | 2010-05-12   |   4| 1
  65 | 2002-07-18   |   3| 3
  65 | 2004-08-05   |   3| 2
  65 | 2007-08-15   |   3| 1
  86 | 2001-05-29   |   5| 5
  86 | 2002-04-04   |   5| 4
  86 | 2006-03-02   |   5| 3
  86 | 2008-02-13   |   5| 2
  86 | 2011-01-19   |   5| 1

Any help would be appreciated.


SELECT id,
   date,
   score_count,
   row_number() over (partition by id order by date desc) as order_value
FROM your_table


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] ordering by date for each ID

2011-05-12 Thread Samuel Gendler 
On Thu, May 12, 2011 at 1:45 PM, Thomas Kellerer  wrote:

> Nguyen,Diep T wrote on 12.05.2011 03:59:
>
>>
>> Any help would be appreciated.
>>
>
> SELECT id,
>   date,
>   score_count,
>   row_number() over (partition by id order by date desc) as order_value
> FROM your_table
>
>
>
Or the more general answer to your question is this:
http://www.postgresql.org/docs/8.4/interactive/functions-window.html
There's lots of neat things you can do with window functions.

--sam