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