Need help with select query

[aew_78]

Hello,

I have a table with the following general format:

CREATE TABLE `table_name` (
`id` int(10) unsigned NOT NULL default '0',
`date` date NOT NULL default '0000-00-00',
`value1` double default NULL,
`value2` double default NULL,
PRIMARY KEY (`id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

What I want to do is to retrieve results from this table where the most recent values for the ids are returned with only one returned record per id.

In MySql this works:

SELECT id, date, value1, value2 FROM (SELECT * FROM table_name WHERE date < '2005-01-12' ORDER BY date desc) rt GROUP BY id;

This generates the proper result set for me with only one result per id and that result being the most recent record from the date specified.

But I have heard on some forums and in tutorials that this is not the correct use of the group by clause and that mysql accepts this unconventional use of group by without generating warnings that other database servers would generate because of the lack of aggregate functions for the fields.

My question is this, how would such a query be performed without the use of the group by clause or could it be done without it? I would prefer my sql statements to be as portable as possible but I really need this functionality.

Thanks for any help you can give me on this matter.

[chazzy]

they are correct. MySQL does not adhere to the ansi standard on the group by clause.

One of the problems is with your table structure - you don't have a single primary key (another comment: using id as a column name for something that's not actually id is a bit misleading).

The first step is to create the view of the table with the unique id/date pairs:


select id,max(date) from table_name group by id;


The next step is to find all of the data for the matching rows:


select a.* from table_name a INNER JOIN
(select id,max(date) as date from table_name group by id) b
on b.id=a.id and a.date=b.date
order by something....


I hope this helps.

[aew_78]

Thanks a lot! That is exactly what I was looking for.

Yes the field name 'id' is rather misleading considering the composite key nature of the table. My real field name is 'cik' which represents a single subject in my database such as a company which has financial data that is published periodically. It's funny, I chose that name so it would be clearer.;)

Anyway, I REALLY appreciate you help with this.