Help with order by clause

Posted in 2008

Topics: Connectivity: ESQL/C, 4GL & Embedded SQL

loc — Mon, 22 Dec 2008 11:22:36 -0800 (PST) — source: Usenet: comp.databases.informix

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.

↪ replying to loc

Obnoxio The Clown — Mon, 22 Dec 2008 19:27:37 +0000 — source: Usenet: comp.databases.informix

loc 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? Yes. But only if you cheat. -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com

↪ replying to loc

Jonathan Leffler — Tue, 23 Dec 2008 22:05:36 -0800 — source: Usenet: comp.databases.informix

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 }; select min_date, date, invoice, type from t1, t2 where t1.invoice = t2.invoice order by min_date, invoice, date, type; Since you are using SE, you must list min_date in the select-list as well as in the ORDER BY clause. If you were using IDS, you'd be able to leave it out of the selected data. For your data, the result would look like: 02/01/2007 02/01/2007 181192 Sale 02/01/2007 02/20/2007 181192 Receipt 02/02/2007 02/02/2007 78940 Sale 02/03/2007 02/03/2007 181190 Sale (SQL not formally tested.) -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2008.0513 -- http://dbi.perl.org/ "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." NB: Please do not use this email for correspondence. I don't necessarily read it every week, even.

↪ replying to Jonathan Leffler

loc — Wed, 24 Dec 2008 06:23:38 -0800 (PST) — source: Usenet: comp.databases.informix

On Dec 24, 1:05 am, "Jonathan Leffler" <jleffler.i...@gmail.com> wrote: > On Mon, Dec 22, 2008 at 11:22 AM, loc <c320...@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 };> > select min_date, date, invoice, type > from t1, t2 > where t1.invoice = t2.invoice > order by min_date, invoice, date, type;> > Since you are using SE, you must list min_date in the select-list as > well as in the ORDER BY clause. If you were using IDS, you'd be able > to leave it out of the selected data. > > For your data, the result would look like: > > 02/01/2007 02/01/2007 181192 Sale > 02/01/2007 02/20/2007 181192 Receipt > 02/02/2007 02/02/2007 78940 Sale > 02/03/2007 02/03/2007 181190 Sale > > (SQL not formally tested.) > > -- > Jonathan Leffler #include <disclaimer.h> > Email: jleff...@earthlink.net, jleff...@us.ibm.com > Guardian of DBD::Informix v2008.0513 --http://dbi.perl.org/ > "Blessed are we who can laugh at ourselves, for we shall never cease > to be amused." > NB: Please do not use this email for correspondence. > I don't necessarily read it every week, even. Jonathan, This is just what I needed, it works great! Thanks for your help.

↪ replying to loc

Jonathan Leffler — Thu, 25 Dec 2008 12:14:34 -0800 (PST) — source: Usenet: comp.databases.informix

On Dec 24, 6:23 am, loc <c320...@gmail.com> wrote: > On Dec 24, 1:05 am, "Jonathan Leffler" <jleffler.i...@gmail.com> > wrote: > > > > > On Mon, Dec 22, 2008 at 11:22 AM, loc <c320...@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 }; Ooops - there's a "GROUP BY invoice" clause missing in that. > > select min_date, date, invoice, type > > from t1, t2 > > where t1.invoice = t2.invoice > > order by min_date, invoice, date, type;> > > Since you are using SE, you must list min_date in the select-list as > > well as in the ORDER BY clause. If you were using IDS, you'd be able > > to leave it out of the selected data. > > > For your data, the result would look like: > > > 02/01/2007 02/01/2007 181192 Sale > > 02/01/2007 02/20/2007 181192 Receipt > > 02/02/2007 02/02/2007 78940 Sale > > 02/03/2007 02/03/2007 181190 Sale > > > (SQL not formally tested.) > Jonathan, This is just what I needed, it works great! Thanks for your help. Good!

Glossary

4GL (Informix-4GL)