Printable View
I have been using SQL Server for a number of years, but continue to have problems with getting multiple MAX values out of one table.
What I am trying to do is simple:
I need to find out what the last date of modification was for each item in a product table. That's it.
Does anybody have any ideas?
create table modi (id int, date datetime)
insert into modi select 1,'2003-08-01 4:24am'
insert into modi select 3,'2003-09-11 4:24am'
insert into modi select 2,'2003-10-01 4:24am'
insert into modi select 1,'2003-11-30 4:24am'
insert into modi select 1,'2003-12-01 4:24am'
insert into modi select 2,'2003-08-21 4:24am'
insert into modi select 3,'2003-08-11 4:24am'
insert into modi select 3,'2003-02-11 4:24am'
insert into modi select 2,'2003-03-01 4:23am'
insert into modi select 3,'2003-04-01 9:24am'
insert into modi select 2,'2003-04-01 7:54am'
insert into modi select 3,'2003-04-01 5:54am'
insert into modi select 1,'2003-04-01 4:24am'
insert into modi select 1,'2003-04-01 5:22am'
--select * from modi order by id,date desc
select id,max(date) from modi group by id
I probably should have mentioned that there are 2 tables - one with products and one with vendors. I need to join these and get unique max values.
Also, I am using Sybase (if that matters).
Thanks!
Can you post the schema of tables and sample values for them and the result you want.
Database may or may not matter depending on the complexity of what you are trying to achieve.
Table: vendor_item_cost
vendor_id (int) pk, fk
cost (decimal)
effective_date (datetime)
item_number (int)
Table: vendor_item
vendor_id (int) pk, fk
item_description (char)
What I am trying to do is get all of the posted costs for all items for all vendors that have a particular vendor id and item number. The posted cost is the last posted date, for example if an item had a posted cost back in 2000 and others were posted this month, I need to get a list of all posted costs for all items regardless of date.
What I have done so far is (in Sybase):
SELECT DISTINCT vencost.item_number, vencost.vendor_id, vencost.cost, ven.item_description, MAX(vencost.effective_date) AS MaxDate FROM vendor_item_cost AS vencost KEY JOIN vendor_item AS ven ON vencost.vendor_id = ven.vendor_id WHERE vencost.vendor_id IN (SELECT DISTINCT vendor_id FROM vendor_item) GROUP BY
vencost.item_number, vencost.vendor_id, vencost.cost, ven.item_description
Please help!
vendor_id alone in vendor_item_cost can't be a primary key, because one vendor may have multiple items and costs.
In absence of a single column primary key i am assuming that vendor_id, item_number and effective_date uniquely identifies a record in vendor_item_cost table.
I am not sure whether sybase supports a derived table so this query may or may not work for you.
select vencost.vendor_id, vencost.item_number, ven.item_description, vencost.cost, vencost.effective_date
from vendor_item_cost AS vencost
KEY JOIN vendor_item AS ven
ON vencost.vendor_id = ven.vendor_id
KEY JOIN
(select vencost.vendor_id, vencost.item_number, MAX(vencost.effective_date) as effective_date
from vendor_item_cost AS vencost
group by vencost.vendor_id, vencost.item_number) as lastcost
ON vencost.vendor_id = lastcost.vendor_id and vencost.item_number = lastcost.item_number and vencost.effective_date = lastcost.effective_date
Thank you! I had to make some minor modifications to the script, but it worked!!!