THE SQL CHALLENGE!
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