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',
date datetime default NULL,
vendor smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (id)
Here's some sample data. I want my query to only return the second row, the one with the later date.
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.