Re: SQL to sort based on a field condition

From: Peter Robson <pgro_at_bgs.ac.uk>
Date: Fri, 25 Jun 2004 14:33:31 +0100
Message-ID: <149828504.20040625143331@bgs.ac.uk>

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_at_freelists.org
sl> put 'unsubscribe' in the subject line.
sl> --
sl> Archives are at http://www.freelists.org/archives/oracle-l/

sl> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

sl> -----------------------------------------------------------------

mailto:pgro_at_bgs.ac.uk

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_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:30:18 CDT