Re: THE SQL CHALLENGE!
Date: 3 Sep 1994 18:38:16 +0100
Message-ID: <34aca8$n2i_at_crocus.csv.warwick.ac.uk>
In article <3478u6$lit_at_charm.magnus.acs.ohio-state.edu>,
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???
No, not properly; I'm fairly sure that you can't do this in "one pass" if, by that, you mean without some sort of subquery; but I'm well used to being corrected :-)
The best that I can think of is something like
Select StockNumber,
Max (To_Char (LastInvDate, 'YYMMDD') || '_at_' ||
Product || '_at_' || To_Char (LastInvQty))
From YourTable
Where Months_Between (To_Date ('&Given_Date', 'MM/DD/YY'), LastInvDate) >= 0
Group By StockNumber;
and you have to sort out how to display the non-StockNumber columns. You could repeat that Max (...) function a load of times within the Select clause, if you want, to get the non-StockNumber columns out as if they were simply listed in a 'less complex' Select clause, but this depends on how much processing you want to do on the data once you've selected it.
>Many Thanxs,
You're welcome, but I'm not sure if this is sufficiently "one pass" enough for you.
> P.
Hank Robinson
Oracle DBA
University of Warwick
Received on Sat Sep 03 1994 - 19:38:16 CEST