MS SQL Data Warehousing

Author

Select Query

Sam

2005-07-14, 9:23 am

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

souri challa

2005-07-14, 9:23 am

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

HTH

Sai

2005-07-14, 9:23 am

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

Aaron Bertrand [SQL Server MVP]

2005-07-14, 9:23 am

> 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

Alejandro Mesa

2005-07-14, 11:23 am

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:

> 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
>
>
>

--CELKO--

2005-07-15, 8:23 pm

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[color=dar
kred]
>= (SELECT MAX(order_date)
FROM Foobar AS F2
WHERE F1.inventory_id = F2.inventory_id
AND F2. order_date
> (SELECT MAX(order_date)
FROM Foobar AS F3
WHERE F1.inventory_id =
F3.inventory_id) );

Sreejith

2005-08-10, 7:23 am

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

"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
>
>
>

Sponsored Links