dcsimg
Results 1 to 7 of 7

Thread: Getting several MAX values in one SQL statement

  1. #1
    Join Date
    Aug 2003
    Posts
    15

    Getting several MAX values in one SQL statement

    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?

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    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

  3. #3
    Join Date
    Aug 2003
    Posts
    15
    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!

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,915
    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.

  5. #5
    Join Date
    Aug 2003
    Posts
    15
    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!

  6. #6
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,915
    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

  7. #7
    Join Date
    Aug 2003
    Posts
    15
    Thank you! I had to make some minor modifications to the script, but it worked!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •