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

Thanks for your help.

However I am getting this error when I run the query


Server: Msg 1033, Level 15, State 1, Line 4
The ORDER BY clause is invalid in views, inline functions, derived
tables,
and subqueries, unless TOP is also specified.

Also I have multiple InventoryID's. Is this going to give me the top2
for each InventoryID.

Thanks

S COmmar



*** Sent via Developersdex http://www.developersdex.com ***

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

Hi

When I run this one it says

Server: Msg 137, Level 15, State 2, Line 3
Must declare the variable '@'.

THanks for ur assistance.

S Commar



*** Sent via Developersdex http://www.developersdex.com ***

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

Hi
Thanks for your help

When I run the query all I am getting is two records. I wanted to get
the records for each inventoryid. The total no of invnetory ID;s is
apporox 4000.

Thanks for ur help.

Here is what I am getting now:

VendorID         No Column Name

LONELE001    732098.0
XERCOR001    732098.0

Thansk

S Commar








*** Sent via Developersdex http://www.developersdex.com ***

You said:

What I am trying to do is basically get the last 2 vendors from whom we
bought the Inventory  item.
So, in order to prevent further discussion, misunderstanding, and wasted
effort on different solutions to various interpretations of your problem,
please provide clear specifications based on the instructions here:
http://www.aspfaq.com/5006

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


Show quoteHide quote

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
                      FROM Foobar AS F2
                   WHERE F1.inventory_id = F2.inventory_id
                          AND F2.order_date
                                    FROM Foobar AS F3
                                  WHERE F1.inventory_id =
F3.inventory_id) );

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

Show quoteHide quote

Multilanguage support
Number of records for Month X, created between 7AM and Noon
sorting text numerically
If statement Problems
Multiple covering indexes on single-row table