THE SQL CHALLENGE!

From: Adrian Wilson <awilson_at_intermec.com>
Date: 2 Sep 94 20:21:41 GMT
Message-ID: <2760_at_intermec.UUCP>

phart_at_magnus.acs.ohio-state.edu (Patrick S Hart) writes:

>Hi Folks!
>
>I have been trying, to no avail, to solve the below problem with only one pass
>at the data. I was curious to know if anyone can solve this:
>
>Table Definition:
>
> StockNumber Product LastInvDate LastInvQty
>-------------------------------------------------------------
> NSN4567 Shoes 08/12/94 1,000
> NSN4567 Shoes 09/01/94 1,500
> NSN2345 Pants 08/23/94 1,200
> NSN2345 Pants 08/05/94 1,500
>
>
>I want to select the *last* recorded Inventory for a given date. So if the
>date
>given is 09/02/94, the result should look like this:
>
> NSN4567 Shoes 09/01/94 1,500
> NSN2345 Pants 08/23/94 1,200
>
>I have not been able to do this in one pass. Anybody know how???
>
>Many Thanxs,
>P.
>

I created a table representing your fields.

SQL> select * from mytable;

STOCKNUMBE PRODUCT    LASTINVDAT LASTINVQTY                                     
---------- ---------- ---------- ----------                                     
NSN4567    Shoes      12-AUG-94        1000                                     
NSN4567    Shoes      01-SEP-94        1500                                     
NSN2345    Pants      23-AUG-94        1200                                     
NSN2345    Pants      05-AUG-94        1500                                     

Try this query:

   select a.stocknumber,

          a.product,
          to_char(max(a.lastinvdate), 'MM/DD/YY') lastinvdate,
          a.lastinvqty

   from mytable a
   where a.lastinvdate = ( select max(b.lastinvdate)

                             from   mytable b
                             where  a.stocknumber = b.stocknumber
                            and    a.product = b.product
                          )
   group by
          a.stocknumber,
          a.product,
          a.lastinvqty

   /

STOCKNUMBER PRODUCT    LASTINVDATE LASTINVQTY                                    
----------- ---------- ----------- ----------                                    
NSN2345     Pants      08/23/94          1200                                    
NSN4567     Shoes      09/01/94          1500                                    

SQL> spool off

Good Luck.

Adrian Wilson

           ____________  ___________  ________________
          /\ LINCROSS  \/\ SOFTWARE \/\ SYSTEMS, INC. \
         /  \___________\ \__________\ \_______________\
         \  /           / /          / /               /
          \/___________/\/__________/\/_______________/
                      Seattle, Washington
                      Tel: (206) 236-5847
       "The pursuit of excellence and competitive advantage"

Received on Fri Sep 02 1994 - 22:21:41 CEST