Need help formulating query

[groston]

Consider a DB with two tables:

PurchaseOrder
PO_ID as int
Username as varchar
(etc)

Items
Item_ID as int
PO_ID as int
Description as varchar
(etc)

What I want to do is the following

Select * from items
where description is distinct
and item_id is maximum item_id for the user and description
and username is 'myUser'

In other words, create a table that has as many rows as there are distinct items that the user has ever ordered and fill in the rest of the row with the data from the most recent (Item_ID monotonically increases) other (etc) data for the item.

Just to make this clear, here is an example (Items table only - assume that we have already filtered for 'myUser'):

Item_ID Description Other columns
1 gloves etc1
2 boots etc2
3 gloves etc3

after the query, the resultant table should be:

Item_ID Description Other columns
2 boots etc2
3 gloves etc3

The following query:

SELECT DISTINCT description
FROM items, purchaseorder
WHERE purchaseorder.username='myUser' and
items.id_po=purchaseorder.id_po

is, I think, part of the answer as it returns a table with the distinct items ordered by the specified user. However, I have been unable to associate the (etc) data of the most recent acquisition of the described item to the description.

Your help is appreciated.

Thanks.


----

Gerry Roston
gerry@pairofdocs.net

 

[AtomicWedgie]

select max(item_id), desctription, other columns
from purchaseorder, items
where purchaseorder.po_id = items.po_id
and purchaseorder.username = 'myUser'
group by description, other columns

 

[groston]

AtomicWedgie,

Thanks for the reply, but this does not work. It returns all instances of items ordered by myUser, not just the most recent order for each unique description. The problem is that 'other columns' are different from each other (which is why I want the most recent) and when you do a 'group by' on these columns, each entry winds up being distinct.


----

Gerry Roston
gerry@pairofdocs.net

 

[Lekar]

SELECT DISTINCT description
FROM items, purchaseorder
WHERE purchaseorder.username='myUser' and
items.id_po=purchaseorder.id_po
And Item_ID = (Select Max(Item_ID)
From Items I
Where I.Description = Description)

 

[groston]

Lekar,

Thanks for the reply, but your answer doesn't quite work :-(.

I modified your example ever so slightly:

SELECT description, quantity, unitprice
FROM items, purchaseorder
WHERE purchaseorder.username='myUser' and
items.id_po=purchaseorder.id_po
And Item_ID = (Select Max(Item_ID)
From Items I
Where I.Description = items.Description)

But here's the problem - the subquery pulls the Item_ID for the most recent entry of the item regardless of the user who made said entry. So, when the outer query does its thing, it will fail to match 'Item_ID = (Select Max(Item_ID)' if the most recent entry of the item description was by another user.

----

Gerry Roston
gerry@pairofdocs.net

 

[groston]

Lekar,

However, you did provide the necessary light!
This is what works:

SELECT description, quantity, unitprice
FROM items, purchaseorder
WHERE purchaseorder.username='myUser' and
items.id_po=purchaseorder.id_po
And Item_ID = (Select Max(Item_ID)
From Items I, purchaseorder p
Where I.Description = items.Description and
p.username='myUser' and
i.id_po=p.id_po)

Havign said this, I find that this can be further simplified:

SELECT description, quantity, unitprice
FROM items
WHERE Item_ID = (Select Max(Item_ID)
From Items I, purchaseorder p
Where I.Description = items.Description and
p.username='myUser' and
i.id_po=p.id_po)

Thanks all for your help!

----

Gerry Roston
gerry@pairofdocs.net