|
Oops! When it hit my inbox I noticed that the statement is missing part of the JOIN. Here is the correct version: Select a.OrderNo, a.Customer, b.Seqno, b.Product From Orders a Join Items b On a.OrderNo = b.OrderNo Where b.Seqno = ( Select Min(c.Seqno) From Items c Where c.OrderNo = a.OrderNo Group by c.OrderNo ) Sorry! John Taylor Canada ----- Original Message ----- From: "John Taylor" <john.taylor@telusplanet.net> To: <MIDRANGE-L@midrange.com> Cc: <bvstone@taylorcorp.com> Sent: Thursday, June 29, 2000 17:36 Subject: Re: SQL Question > Brad, > > As far as I know, the DB2/400 SQL implementation does not provide a way to > specify the number of records per matching set that you want returned. > Therefore, you need a sub-select. You can try something like this: > > For a complete list of orders joined to the first item record: > > Select a.OrderNo, a.Customer, b.Seqno, b.Product > From Orders a Join Items b > Where b.Seqno = > ( Select Min(c.Seqno) From Items c > Where c.OrderNo = a.OrderNo > Group by c.OrderNo ) > > > I was a bit confused by your request. Your example indicated that you were > working with a single order at a time, however your question included the > line "I have an option for a user to either view all detail lines, or only > the first one of each order." If dealing with a single order at a time, > simply add the appropriate condition to the WHERE clause of the primary > SELECT statement. > > > Regards, > > John Taylor > Canada > > ----- Original Message ----- > From: "Stone, Brad V (TC)" <bvstone@taylorcorp.com> > To: <MIDRANGE-L@midrange.com> > Sent: Thursday, June 29, 2000 13:08 > Subject: SQL Question > > > > Question on SQL... > > > > I have an order detail file. The unique key is invoice number, sequence > > number. I want to only select records that are the first sequence number > > for each invoice. > > > > I've been playing, but I can't figure it out. Something like (not > > syntatically correct) > > > > select * from orddetpf where odcst = '36000' > > order by odinv > > but only select min(seqno) group by invoice > > > > I'd like to do it with a single select (no subquery) if possible. This is > > because I have an option for a user to either view all detail lines, or > only > > the first one of each order. I already have the SQL statment for the > first, > > and would like to just modify it (dynamic SQL) depending on which "mode" > the > > user chooses. > > > > Thanks! > > > > Brad > > +--- > > | This is the Midrange System Mailing List! > > | To submit a new message, send your mail to MIDRANGE-L@midrange.com. > > | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com. > > | To unsubscribe from this list send email to > MIDRANGE-L-UNSUB@midrange.com. > > | Questions should be directed to the list owner/operator: > david@midrange.com > > +--- > > +--- > | This is the Midrange System Mailing List! > | To submit a new message, send your mail to MIDRANGE-L@midrange.com. > | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com. > | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com. > | Questions should be directed to the list owner/operator: david@midrange.com > +--- +--- | This is the Midrange System Mailing List! | To submit a new message, send your mail to MIDRANGE-L@midrange.com. | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com. | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com. | Questions should be directed to the list owner/operator: david@midrange.com +---
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.