Re: SQL to sort based on a field condition
- From: Peter Robson <pgro@xxxxxxxxx>
- To: susan lam <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Jun 2004 14:33:31 +0100
susan,
This looks fun. Couple of points - what is your output stream? If that
is only going to take the rows with f3='Y', then sorting the others
becomes somewhat superfluous. You cannot hold a sorted set in a table
- that is relationally contradictory.
Whatever, I think you could have two distinct operations here, in
which case its easy, of course.
peter
edinburgh
Friday, June 25, 2004, 2:26:29 PM, you wrote:
sl> Hi,
sl> I have the a table and records that I would like to
sl> sort on based on the following conditions:
sl> create table temptable (f1 varchar2(20), f2
sl> varchar2(20), f3 varchar2(1));
sl> 1. sort on the first field, f1 in ascending order
sl> 2. sort on the second field, f2 in ascending order but
sl> if the third field, f3='Y', then list the record first
sl> f3 can only have values ('N','Y') and there can only
sl> be one 'Y' for each distinct f1
sl> Table records
sl> f1 f2 f3
sl> -------------------
sl> ZOO BANANA N
sl> ZOO APPLE N
sl> ZOO ORANGE Y
sl> CASE OWL N
sl> CASE TIGER Y
sl> CASE MONKEY N
sl> Output after sorting
sl> f1 f2 f3
sl> -------------------
CASE TIGER Y ->>rec listed first because f3='Y'
sl> CASE MONKEY N
sl> CASE OWL N
ZOO ORANGE Y ->>rec listed first because f3='Y'
sl> ZOO APPLE N
sl> ZOO BANANA N
sl> How should I write my SQL to achieve that?
sl> TIA
sl> susan
sl> __________________________________________________
sl> Do You Yahoo!?
sl> Tired of spam? Yahoo! Mail has the best spam protection around
sl> http://mail.yahoo.com
sl> ----------------------------------------------------------------
sl> Please see the official ORACLE-L FAQ: http://www.orafaq.com
sl> ----------------------------------------------------------------
sl> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
sl> put 'unsubscribe' in the subject line.
sl> --
sl> Archives are at //www.freelists.org/archives/oracle-l/
sl> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
sl> -----------------------------------------------------------------
--
mailto:pgro@xxxxxxxxx
*********************************************************************
This e-mail message, and any files transmitted with it, are
confidential and intended solely for the use of the addressee. If
this message was not addressed to you, you have received it in error
and any copying, distribution or other use of any part of it is
strictly prohibited. Any views or opinions presented are solely those
of the sender and do not necessarily represent those of the British
Geological Survey. The security of e-mail communication cannot be
guaranteed and the BGS accepts no liability for claims arising as a
result of the use of this medium to transmit messages from or to the
BGS. . http://www.bgs.ac.uk
*********************************************************************
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
