Thread: Show CAS, USD first; the left ordered by currency name

Show CAS, USD first; the left ordered by currency name

From

Emi Lu

Date:

30 July 2009, 19:51:52

Good morning,

I have a currency table (code, description).

Example values: ADF | Andorran Franc ... ... ANG | NL Antillian Guilder AON | Angolan New Kwanza AUD | Australian
DollarAWG | 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

Re: Show CAS, USD first; the left ordered by currency name

From

Andreas Kretschmer

Date:

30 July 2009, 20:05:44

Emi Lu <emilu@encs.concordia.ca> 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
> ...
>

Sure:

test=*# select * from currency order by case when code='USD' then 0 when
code = 'CAD' then 1 end, code;code |     description
------+----------------------USD  | US DollarCAD  | Canadian DollarADF  | Andorran FrancANG  | NL Antillian GuilderAON
|Angolan New KwanzaAUD  | Australian DollarAWG  | Aruban FlorinBBD  | Barbados Dollar
 




Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

Re: Show CAS, USD first; the left ordered by currency name

From

sergey kapustin

Date:

30 July 2009, 20:06:32

<div dir="ltr">...order by currency not in('USD', 'AND', 'CAD');<br /><br />this condition will be avaluated as FALSE
forUSD, AND and CAD, and as TRUE for all other currencies. When the records are sorted the "false" are placed on the
topbecause false<true;.<br /><br /><div class="gmail_quote">On Thu, Jul 30, 2009 at 10:51 PM, Emi Lu <span
dir="ltr"><<ahref="mailto:emilu@encs.concordia.ca">emilu@encs.concordia.ca</a>></span> wrote:<br /><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
Goodmorning,<br /><br /> I have a currency table (code, description).<br /><br /> Example values:<br />  ADF | Andorran
Franc<br/>  ... ...<br />  ANG | NL Antillian Guilder<br />  AON | Angolan New Kwanza<br />  AUD | Australian Dollar<br
/> AWG | Aruban Florin<br />  BBD | Barbados Dollar<br />  USD | US Dollar<br />  CAD | Canadian Dollar<br /><br /> Is
therea way I can query to display USD AND CAD first, while other rows are ordered by Code.<br /><br /> For example,<br
/><br/> CAS | Canadian Dollar<br /> USD | US Dollar<br /> ADF | Andorran Franc<br /> ...<br /><br /><br /> Thanks a
lot!<br/> --<br /> Lu Ying<br /><font color="#888888"><br /><br /><br /> -- <br /> Sent via pgsql-sql mailing list (<a
href="mailto:pgsql-sql@postgresql.org"target="_blank">pgsql-sql@postgresql.org</a>)<br /> To make changes to your
subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-sql"
target="_blank">http://www.postgresql.org/mailpref/pgsql-sql</a><br/></font></blockquote></div><br /></div>

Re: Show CAS, USD first; the left ordered by currency name

From

Harald Fuchs

Date:

31 July 2009, 11:37:29

In article <4A71F9CB.9050805@encs.concordia.ca>,
Emi Lu <emilu@encs.concordia.ca> 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.

Re: Show CAS, USD first; the left ordered by currency name

From

bricklen

Date:

31 July 2009, 13:28:03

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<hari.fuchs@gmail.com> wrote:
> In article <4A71F9CB.9050805@encs.concordia.ca>,
> Emi Lu <emilu@encs.concordia.ca> 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 (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Re: Show CAS, USD first; the left ordered by currency name

From

Emi Lu

Date:

31 July 2009, 14:10:44

"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<true;.
> 
> On Thu, Jul 30, 2009 at 10:51 PM, Emi Lu <emilu@encs.concordia.ca 
> <mailto:emilu@encs.concordia.ca>> 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 (pgsql-sql@postgresql.org
>     <mailto:pgsql-sql@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-sql
> 
>