Re: Help with order by clause

Jonathan Leffler wrote: > On Mon, Dec 22, 2008 at 11:22 AM, loc <c320sky@gmail.com> wrote: >> create temp table t1 (date date, invoice integer, type char(30)); >> insert into t1 values ("020107", 181192, "Sale"); >> insert into t1 values ("020207", 78940, "Sale"); >> insert into t1 values ("020307", 181190, "Sale"); >> insert into t1 values ("022007", 181192, "Receipt");>> >> select * from t1 order by 2,1>> >> date invoice type >> >> 02/02/2007 78940 Sale >> 02/03/2007 181190 Sale >> 02/01/2007 181192 Sale >> 02/20/2007 181192 Receipt >> >> I want to be able to order the above data by date, but to also have >> like invoice numbers next to each other. The data should be ordered >> like: >> >> 02/01/2007 181192 Sale >> 02/20/2007 181192 Receipt >> 02/02/2007 78940 Sale >> 02/03/2007 181190 Sale >> >> Can this be done? This data will be outputted to a 4gl report. Using >> Informix SE 7.25.UC6R1 and 4GL 7.32.UC4. > > > Yes; and no cheating (pace The Clown), just some thinking, is required. > > I think you want items listed in order of earliest date for any item > with a given invoice number, then by invoice number, then by date, and > finally by type (well, the last might not be required, but it ensures > a determinate sequence for the data). > > select invoice, min(date) as min_date from t1 into temp t2 { with no log }; There's the cheat, right there! :op -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com