Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Chicago
    Posts
    2

    Red face Unanswered: selecting the row with the latest date!

    Hi, this is my first post to dbforums, glad to join the community!

    I'm having a problem with a MySQL query: I have a table where vendors can submit price quotes for various part numbers. When a specific vendor has submitted more than one quote for a part, I have duplicate entries in my table, except for a different datetime field. I am ok with these "duplicate" entries, I'd rather they stay there until I later delete them. I am trying to construct a query that will pull all part numbers that have been quoted by ANY of the vendors. The problem is, when a vendor has quoted a part more than once, both rows are selected. I want to select the row with the LATEST date field.

    Here is my code for the table itself:

    CREATE TABLE submit (
    id int(10) unsigned NOT NULL auto_increment,
    partno varchar(25) NOT NULL default '',
    price float unsigned NOT NULL default '0',
    notes text,
    date datetime default NULL,
    vendor smallint(5) unsigned NOT NULL default '0',
    PRIMARY KEY (id)
    ) TYPE=InnoDB;

    Here's some sample data. I want my query to only return the second row, the one with the later date.

    partno | price | notes | date | vendor
    205005 | 0.12345 | NULL | 2003-01-31 14:28:48 | 1
    205005 | 0.7 | later price | 2003-01-31 14:30:06 | 1

    --------------------------------------

    Thanks for any input or ideas, as I have searched for hours and hours and found no direction with this.

    Regards,
    Devin Breen

  2. #2
    Join Date
    Jan 2003
    Location
    Chicago
    Posts
    2

    band-aid solution

    Well, I've been coding since I originally posted and have come up with a "band-aid" solution.

    After getting nowhere with self-joins, I decided to create a temporary table which stores the partnumber and max(date) for all items quoted by vendor x. I then joined this temp. table with the original submission table to get the tuples matching the date and part number from my temporary table.

    It is too bad that my version of MySQL cannot do subselects. I understand that in ver 4.1 this will be available, or is currently. I would much rather do without temp. tables, but I cannot see any way around this.

    Regards,
    Devin Breen

  3. #3
    Join Date
    Feb 2003
    Location
    West Sussex, UK
    Posts
    5

    Re: selecting the row with the latest date!

    Devin,

    Can't you use a SELECT statement with the 'ORDER BY date DESC LIMIT 1' clause on the end?

    SynCid

  4. #4
    Join Date
    Feb 2003
    Location
    Amsterdam
    Posts
    8

    Post Aggregate function Max()

    This will probably work quicker than using temp tables:

    Code:
    select partno, price,  notes, max(date), vendor 
    from submit 
    group by partno, price,  notes, vendor

Posting Permissions

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