Re: SQL Question

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
+---