Re: [SQL] Show CAS, USD first; the left ordered by currency name
In article <[email protected]>, Emi Lu writes: > Good morning, > I have a currency table (code, description). > Example values: > ADF | Andorran Franc > ... ... > ANG | NL Antillian Guilder > AON | Angolan New Kwanza > AUD | Australian Dollar > AWG | Aruban Florin > BBD | Barbados Dollar > USD | US Dollar > CAD | Canadian Dollar > Is there a way I can query to display USD AND CAD first, while other > rows are ordered by Code. > For example, > CAS | Canadian Dollar > USD | US Dollar > ADF | Andorran Franc > ... Probably the shortest solution is SELECT code, description FROM currency ORDER BY code != 'CAD', code != 'USD', code; BTW: your data are obsolete. Andorra has the Euro. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Show CAS, USD first; the left ordered by currency name
Alternatively,
...
ORDER BY (case when code in ('USD','CAD') then 0 else 1 end),code
On Fri, Jul 31, 2009 at 4:37 AM, Harald Fuchs wrote:
> In article <[email protected]>,
> Emi Lu writes:
>
>> Good morning,
>> I have a currency table (code, description).
>
>> Example values:
>> ADF | Andorran Franc
>> ... ...
>> ANG | NL Antillian Guilder
>> AON | Angolan New Kwanza
>> AUD | Australian Dollar
>> AWG | Aruban Florin
>> BBD | Barbados Dollar
>> USD | US Dollar
>> CAD | Canadian Dollar
>
>> Is there a way I can query to display USD AND CAD first, while other
>> rows are ordered by Code.
>
>> For example,
>
>> CAS | Canadian Dollar
>> USD | US Dollar
>> ADF | Andorran Franc
>> ...
>
> Probably the shortest solution is
>
> SELECT code, description
> FROM currency
> ORDER BY code != 'CAD', code != 'USD', code;
>
> BTW: your data are obsolete. Andorra has the Euro.
>
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Show CAS, USD first; the left ordered by currency name
"order by code not in ('USD', 'EUR', 'CAD') , code" is exactly what I
was looking for!
Good to know how "order by not in" works and thank you very much for all
your inputs!
--
Lu Ying
...order by currency not in('USD', 'AND', 'CAD');
this condition will be avaluated as FALSE for USD, AND and CAD, and as
TRUE for all other currencies. When the records are sorted the "false"
are placed on the top because false
On Thu, Jul 30, 2009 at 10:51 PM, Emi Lu > wrote:
Good morning,
I have a currency table (code, description).
Example values:
ADF | Andorran Franc
... ...
ANG | NL Antillian Guilder
AON | Angolan New Kwanza
AUD | Australian Dollar
AWG | Aruban Florin
BBD | Barbados Dollar
USD | US Dollar
CAD | Canadian Dollar
Is there a way I can query to display USD AND CAD first, while other
rows are ordered by Code.
For example,
CAS | Canadian Dollar
USD | US Dollar
ADF | Andorran Franc
...
Thanks a lot!
--
Lu Ying
--
Sent via pgsql-sql mailing list ([email protected]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] on error resume next
hi, can i use savepoints to realize something like "on error resume next"? i've got the following situation: begin; 1. create view user001.accounts as select * from base.accounts; 2. grant select on user001.accounts to loginuser001; commit; begin; 3. create view user002.accounts as select * from base.accounts; 4. grant select on user002.accounts to loginuser002; commit; my goal is to avoid execution stop, if one of the transactions fail. let's say line 1 throws an error it should go further to line 3. any ideas? thank you. jasmin
Re: [SQL] SQL report
Did you look at the query plans for the various record counts? That might show which index is missing or misinformed :). I wonder if clustering the status table on objectid would help? This does then require maintenance so you might only load it at 75%. [email protected] wrote: Hi Rob, I have default B-Tree indexes created for each of the indexed columes and primary key columes. (No multiple columes indexe or NULL FIRST or DESC/ASC). I am using PostgreSQL 8.3 with the auto vacuum daemon on. I assume analyse will be automatically run to collect statistics for use by the planner and there is no maintainance for B-tree indexes once it is created. (Please point me out if I am wrong about this) I will probably try to partition the status table to group more recent status records together to minimize the dataset I am querying. Thx John On Jul 31, 2009 1:16am, Rob Sargent wrote: > I would be curious to know the performance curve for let's say 20K, 40K , 60K, 80K, 100K records. And what sort of indexing you have, whether or not it's clustered, re-built and so on. > > > > One could envision partitioning the status table such that recent records were grouped together (on the assumption that they will be most frequently "reported"). > > > > [email protected] wrote: > > > I have the following senario. > > > > I have a tracking system. The system will record the status of an object regularly, all the status records are stored in one table. And it will keep a history of maximum 1000 status record for each object it tracks. The maximum objects the system will track is 100,000. Which means I will potentially have a table size of 100 million records. > > > > I have to generate a report on the latest status of all objects being tracked at a particular point in time, and also I have to allow user to sort and filter on different columes in the status record displayed in the report. > > > > The following is a brief description in the status record (they are not actual code) > > > > ObjectRecord( > > objectId bigint PrimaryKey > > desc varchar > > ) > > > > StatusRecord ( > > id bigint PrimaryKey > > objectId bigint indexed > > datetime bigint indexed > > capacity double > > reliability double > > efficiency double > > ) > > > > I have tried to do the following, it works very well with around 20,000 objects. (The query return in less than 10s) But when I have 100,000 objects it becomes very very slow. (I don't even have patience to wait for it to return I kill it after 30 mins) > > > > select * from statusrecord s1 INNER JOIN ( SELECT objectId , MAX(datetime) AS msdt FROM statusrecord WHERE startDatetime > > > I did try to write a store procedure like below, for 100,000 objects and 1000 status records / object, it returns in around 30 mins. > > > > CREATE OR REPLACE FUNCTION getStatus(pitvalue BIGINT) RETURNS SETOF statusrecord AS $BODY$ > > DECLARE > > id VARCHAR; > > status statusrecord%ROWTYPE; > > BEGIN > > FOR object IN SELECT * FROM objectRecord > > LOOP > > EXECUTE 'SELECT * FROM statusrecord WHERE objectId = ' || quote_literal(object.objectId) || > > ' AND datetime > INTO status; > > IF FOUND THEN > > RETURN NEXT status; > > END IF; > > END LOOP; > > RETURN; > > END > > $BODY$ LANGUAGE plpgsql; > > > > Just wanna to know if anyone have a different approach to my senario. Thanks alot. > > > > John > > -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] on error resume next
Jasmin Dizdarevic wrote:
hi,
can i use savepoints to realize something like "on error resume next"?
i've got the following situation:
begin;
1. create view user001.accounts as select * from base.accounts;
2. grant select on user001.accounts to loginuser001;
commit;
begin;
3. create view user002.accounts as select * from base.accounts;
4. grant select on user002.accounts to loginuser002;
commit;
my goal is to avoid execution stop, if one of the transactions fail.
let's say line 1 throws an error it should go further to line 3.
any ideas?
thank you.
jasmin
AFAIK it's not possible. A transaction is kind of a container with a
positive or negative result. If one of the queries fails in between a
transaction, it will be rolled back after a commit. What you can do with
savepoints is the following:
usage=# CREATE TABLE test (id serial, content text);
usage=# BEGIN;
usage=# INSERT INTO test (content) VALUES ('first stuff');
usage=# SAVEPOINT s1;
usage=# INSERT INTO test (content) VALUES ();
ERROR: syntax error at or near ")"
usage=# ROLLBACK TO SAVEPOINT s1;
ROLLBACK
usage=# SELECT * FROM test;
id | content
+--
1 | first stuff
(1 row)
usage=# COMMIT;
COMMIT
usage=# SELECT * FROM test;
id | content
+--
1 | first stuff
(1 row)
The second INSERT statement fails. If you would go further with insert
statements and then fire a COMMIT at the end, nothing would be inserted
into the table. But if you fire a ROLLBACK TO SAVEPOINT s1, at least the
data of the first INSERT statement are written.
So maybe this is a start help for creating some logic to get something
like 'on error resume next'.
Cheers
Andy
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] on error resume next
Hi,
that's not really that what i need. I think i will solve it on client side.
thank you.
jasmin
2009/7/31 Andreas Wenk
> Jasmin Dizdarevic wrote:
>
>> hi,
>> can i use savepoints to realize something like "on error resume next"?
>> i've got the following situation:
>> begin;
>> 1. create view user001.accounts as select * from base.accounts;
>> 2. grant select on user001.accounts to loginuser001;
>> commit;
>> begin;
>> 3. create view user002.accounts as select * from base.accounts;
>> 4. grant select on user002.accounts to loginuser002;
>> commit;
>> my goal is to avoid execution stop, if one of the transactions fail.
>> let's say line 1 throws an error it should go further to line 3.
>> any ideas?
>> thank you.
>> jasmin
>>
>
> AFAIK it's not possible. A transaction is kind of a container with a
> positive or negative result. If one of the queries fails in between a
> transaction, it will be rolled back after a commit. What you can do with
> savepoints is the following:
>
> usage=# CREATE TABLE test (id serial, content text);
> usage=# BEGIN;
> usage=# INSERT INTO test (content) VALUES ('first stuff');
> usage=# SAVEPOINT s1;
> usage=# INSERT INTO test (content) VALUES ();
> ERROR: syntax error at or near ")"
> usage=# ROLLBACK TO SAVEPOINT s1;
> ROLLBACK
> usage=# SELECT * FROM test;
> id | content
> +--
> 1 | first stuff
> (1 row)
>
> usage=# COMMIT;
> COMMIT
> usage=# SELECT * FROM test;
> id | content
> +--
> 1 | first stuff
> (1 row)
>
> The second INSERT statement fails. If you would go further with insert
> statements and then fire a COMMIT at the end, nothing would be inserted into
> the table. But if you fire a ROLLBACK TO SAVEPOINT s1, at least the data of
> the first INSERT statement are written.
>
> So maybe this is a start help for creating some logic to get something like
> 'on error resume next'.
>
> Cheers
>
> Andy
>
>
>
>
[SQL] How fetch multiple rows into one text-field?
Hi, how can I fetch multiple rows into one text-field? I need the contents of some log-infos condensed into a single text to show in a report. There is a log with a single record per event. The log is like this (log_id, case_id, log_date, log_category, log_notes) I need a date-ordered list of log-date, -category, -notes of all log-events of the same case-id like log_date || ' ' || log_category || ' : ' || log_nots e.g. 20.07.2009 Category 17 : utterly noteworthy notes 21.07.2009 Category 42 : lots more 22.07.2009 Category 17 : still more drivel The report consists of a couple of case-infos and should look like case_id, case_name, case_all_log_events Is there a way to do this? regards andreas -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] How fetch multiple rows into one text-field?
On Fri, Jul 31, 2009 at 10:09:46PM +0200, Andreas wrote: > Hi, > > how can I fetch multiple rows into one text-field? > I need the contents of some log-infos condensed into a single text to > show in a report. It sounds like you might want something like this: SELECT ... array_to_string(array_accum(log_notes, '')) FROM... You might need to add array_accum manually; before 8.4 it wasn't built in. See http://www.postgresql.org/docs/8.3/static/xaggr.html -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
