| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL to sort based on a field condition
SQL> select f1,f2,f3
2 from temptable
3 order by f1, f3 desc, f2
4 /
F1 F2 F -------------------- -------------------- - CASE TIGER Y CASE MONKEY N CASE OWL N ZOO ORANGE Y ZOO APPLE N ZOO BANANA N
6 rows selected.
Regards,
Larry G. Elkins
elkinsl_at_flash.net
214.954.1781
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of susan lam
> Sent: Friday, June 25, 2004 8:26 AM
> To: oracle-l_at_freelists.org
> Subject: SQL to sort based on a field condition
>
>
> Hi,
> I have the a table and records that I would like to
> sort on based on the following conditions:
>
> create table temptable (f1 varchar2(20), f2
> varchar2(20), f3 varchar2(1));
>
> 1. sort on the first field, f1 in ascending order
> 2. sort on the second field, f2 in ascending order but
> if the third field, f3='Y', then list the record first
>
>
> f3 can only have values ('N','Y') and there can only
> be one 'Y' for each distinct f1
>
>
> Table records
> f1 f2 f3
> -------------------
> ZOO BANANA N
> ZOO APPLE N
> ZOO ORANGE Y
> CASE OWL N
> CASE TIGER Y
> CASE MONKEY N
>
>
> Output after sorting
> f1 f2 f3
> -------------------
> CASE TIGER Y ->rec listed first because f3='Y'
> CASE MONKEY N
> CASE OWL N
> ZOO ORANGE Y ->rec listed first because f3='Y'
> ZOO APPLE N
> ZOO BANANA N
>
>
>
> How should I write my SQL to achieve that?
>
> TIA
>
> susan
>
> __________________________________________________
> Do You Yahoo!?
> Tired of spam? Yahoo! Mail has the best spam protection around
> http://mail.yahoo.com
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Fri Jun 25 2004 - 08:37:03 CDT
 |
 |