Try,
use northwind
go
create table t1 (
InvetoryID varchar(25),
DateofOrder datetime,
VendorID varchar(25)
)
go
insert into t1 values('ACCKAPCONS01', '05/05/2005', 'ALTPRO001')
insert into t1 values('ACCKAPCONS01', '05/05/2005', 'ALTPRO002')
insert into t1 values('ACCKAPCONS01', '05/05/2005', 'ALTPRO003')
insert into t1 values('ACCKAPCONS01', '04/03/2005', 'ALTPRO001')
insert into t1 values('ACCKAPCONS02', '04/02/2005', 'TRAP00001')
insert into t1 values('ACCKAPCONS02', '04/01/2005', 'ALTPRO001')
go
select
*
from
t1 as a
where
(
select
count(*)
from
t1 as b
where
b.InvetoryID = a.InvetoryID
and
(
b.DateofOrder > a.DateofOrder
or
(
b.DateofOrder = a.DateofOrder
and b.VendorID >= a.VendorID
)
)
) < 3
go
drop table t1
go
AMB
[quoted text, click to view] "Sam" wrote:
> I have a POITEM Table which has just three columns
>
> InvetoryID, DateofOrder, VendorID
>
> I have under the same InventoryID ,multiple DateofOrders and VendorrID;s.
>
> SO for eg here is a couple of lines of the data,
>
> InvetoryID, DateofOrder, VendorID
> ACCKAPCONS01 05/05/2005 ALTPRO001
> ACCKAPCONS01 04/03/2005 ALTPRO001
> ACCKAPCONS02 04/02/2005 TRAP00001
> ACCKAPCONS02 04/01/2005 ALTPRO001
>
>
> What I am trying to do is basically get the last 2 vendors from whom we
> bought the Inventory item.
>
> In some cases we would have just bought it from one vendor and in some cases
> we might have bought it from different vendors at different times. ( As e.g
> above)
>
> What query can I run that will tell me the last two vendors (if last 2 are
> the same it should look for the next order with a different vendorID and
> keep going till it finds a different Vendor ID if it exists)
>
> Thanks so much for your assistance.
>
> S Commar
>
>
I have a POITEM Table which has just three columns
InvetoryID, DateofOrder, VendorID
I have under the same InventoryID ,multiple DateofOrders and VendorrID;s.
SO for eg here is a couple of lines of the data,
InvetoryID, DateofOrder, VendorID
ACCKAPCONS01 05/05/2005 ALTPRO001
ACCKAPCONS01 04/03/2005 ALTPRO001
ACCKAPCONS02 04/02/2005 TRAP00001
ACCKAPCONS02 04/01/2005 ALTPRO001
What I am trying to do is basically get the last 2 vendors from whom we
bought the Inventory item.
In some cases we would have just bought it from one vendor and in some cases
we might have bought it from different vendors at different times. ( As e.g
above)
What query can I run that will tell me the last two vendors (if last 2 are
the same it should look for the next order with a different vendorID and
keep going till it finds a different Vendor ID if it exists)
Thanks so much for your assistance.
S Commar
