Re: SQL Question

-----Original Message-----
From: Stone, Brad V (TC) <bvstone@taylorcorp.com>
To: 'MIDRANGE-L@midrange.com' <MIDRANGE-L@midrange.com>
Date: Thursday, June 29, 2000 5:38 PM
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.
>


Uh, pardon my ignorance here, but isn't the first sequence number of each
order a 1?  If not, why not?

The answer would then be apparent...  select * from orddetpf where seqno = 1
and ...

If the sequence number for each order does not start with one, then you may
have a problem doing this without a subselect.

select a.* from orddetpf a
  where a.odcst = '36000'
       and a.seqno = (select min(seqno) from orddetpf b
                                       where b.odcst = a.odcst and b.odinv
= a.odinv)


This is ugly, but actually over 1 million+ records ran in about 6 seconds
interactive on an S20, single processor, low end.

Of course, your mileage may vary.  The file that I ran a similar expression
over has indexes for most of the referenced fields that I was using.

===========================================================
R. Bruce Hoffman, Jr.
 -- IBM Certified AS/400 Professional System Administrator
 -- IBM Certified AS/400 Professional Network Administrator
 -- IBM Certified Specialist - AS/400 Administrator
 -- IBM Certified Specialist - RPG IV Developer

"If all you have is RPG, then everything looks like a 400!"



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