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

Try using
Select Top 2 VendorID From (Select Distinct VendorId
From <table>
Order by DateOfOrder Desc
) Vendors

HTH

Its not an optimized solution, but it works

DECLARE @t TABLE(VendorID VARCHAR(255))
INSERT INTO @t SELECT VendorID from POITEM ORDER BY DateofOrder DESC
SELECT DISTINCT TOP 2 * FROM @t

Order by what? Inserting into @t in a specific order does not mean your
data will be stored that way, or will be retrieved in that order when
selected with an ORDER BY clause. Also, not sure why an intermediate table
is necessary here. How about:

SELECT TOP 2 VendorID, MAX(DateOfOrder)
FROM POITEM
GROUP BY VendorID
ORDER BY 2 DESC

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


"Sam" wrote:

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

Can I guess that you really meant to post this and that you knew to use
the proper ISO date formats?

CREATE TABLE Foobar
(inventory_id CHAR (12) NOT NULL
CHECK (inventory_id LIKE
'[A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][A-Z][0-9][0-9]'),
order_date DATETIME NOT NULL
vendor_id CHAR (12) NOT NULL
CHECK (vendor_id LIKE
'[A-Z][A-Z][A-Z][A-Z][A-Z][0-9][0-9][0-9][0-9]'),
PRIMARY KEY (inventory_id CHAR (12) NOT NULL
CHECK (inventory_id, order_date));

SELECT DISTINCT inventory_id, vendor_id
FROM Foobar AS F1
WHERE order_date
WHERE F1.inventory_id = F2.inventory_id
AND F2.order_date
WHERE F1.inventory_id =
F3.inventory_id) );

Use => Select GROUP BY with TIES... This will solve your problem...

"Sam" wrote: