SQL to sort based on a field condition

From: susan lam <susanzlam_at_yahoo.com>
Date: Fri, 25 Jun 2004 06:26:29 -0700 (PDT)
Message-ID: <20040625132630.89664.qmail@web53302.mail.yahoo.com>

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));

sort on the first field, f1 in ascending order
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


-----------------------------------------------------------------

Received on Fri Jun 25 2004 - 08:23:10 CDT