|
Would, select * from table order by contract, flag desc, master work? Thanks, Mark Mark D. Walter Senior Programmer/Analyst CCX, Inc. mwalter@xxxxxxxxxx http://www.ccxinc.com |---------+-------------------------------> | | MWalter@hanoverwire.| | | com | | | Sent by: | | | midrange-l-bounces@m| | | idrange.com | | | | | | | | | 01/26/2004 04:32 PM | | | Please respond to | | | Midrange Systems | | | Technical Discussion| | | | |---------+-------------------------------> >------------------------------------------------------------------------------------------------------------------------------| | | | To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx> | | cc: | | Subject: RE: sql help | >------------------------------------------------------------------------------------------------------------------------------| That's true. I stand corrected. Thanks, Mark Mark D. Walter Senior Programmer/Analyst CCX, Inc. mwalter@xxxxxxxxxx http://www.ccxinc.com |---------+-------------------------------> | | "Walden H. Leverich | | | III" | | | <WaldenL@TechSoftInc| | | .com> | | | Sent by: | | | midrange-l-bounces@m| | | idrange.com | | | | | | | | | 01/26/2004 04:13 PM | | | Please respond to | | | Midrange Systems | | | Technical Discussion| | | | |---------+-------------------------------> >------------------------------------------------------------------------------------------------------------------------------| | | | To: "'Midrange Systems Technical Discussion'" <midrange-l@xxxxxxxxxxxx> | | cc: | | Subject: RE: sql help | >------------------------------------------------------------------------------------------------------------------------------| >select * from table order by flag desc, contract, master The problem with that is that he'll get all the masters up front. I think he's looking for a result set that has the master followed immediately by its children. Assuming that the "blank" master ids are null: select * from c order by coalesce(master, contract), Flag desc, contract Would provide the requested result. The coalesce function return the first non-null function. So for the master contracts where master is null it will return contract for the child contracts it will return master. -Walden ------------ Walden H Leverich III President & CEO Tech Software (516) 627-3800 x11 (208) 692-3308 eFax WaldenL@xxxxxxxxxxxxxxx http://www.TechSoftInc.com Quiquid latine dictum sit altum viditur. (Whatever is said in Latin seems profound.) -----Original Message----- From: MWalter@xxxxxxxxxxxxxxx [mailto:MWalter@xxxxxxxxxxxxxxx] Sent: Monday, January 26, 2004 3:49 PM To: Midrange Systems Technical Discussion Subject: Re: sql help select * from table order by flag desc, contract, master Thanks, Mark Mark D. Walter Senior Programmer/Analyst CCX, Inc. mwalter@xxxxxxxxxx http://www.ccxinc.com |---------+-------------------------------> | | "Smith, Mike" | | | <Mike_Smith@RGCResou| | | rces.com> | | | Sent by: | | | midrange-l-bounces@m| | | idrange.com | | | | | | | | | 01/26/2004 03:08 PM | | | Please respond to | | | Midrange Systems | | | Technical Discussion| | | | |---------+-------------------------------> >--------------------------------------------------------------------------- ---------------------------------------------------| | | | To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx> | | cc: | | Subject: sql help | >--------------------------------------------------------------------------- ---------------------------------------------------| I have a file that has a contract#, a flag that indicates if the file is a master contract, and a master contract# so i might have something like contract flag master 1 y 2 y 3 n 1 4 n 2 5 n 1 where contract 1 is a master contract with subordinate contracts 3 and 5 i'm trying to create an SQL statement that will list the master contracts and the subordinate contracts. So far i haven't been able to get this to work. Can i do this in 1 statement. i want results such as contract flag master 1 y 3 n 1 5 n 1 2 y 4 n 2 _______________________________________________ This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. _______________________________________________ This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. _______________________________________________ This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l. _______________________________________________ This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/midrange-l or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/midrange-l.
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
