August 11, 2006 at 2:22 pm
Can you assist me with a query to select, for each machine_id and setting_type_id (group by), the setting_value determined by the latest change_date?
create table machine_settings(machine_id, setting_type_id, setting_value, change_date)
go
insert into machine_settings values ('1', 'width', 5, '12/17/2005 10:45:15 AM')
insert into machine_settings values ('2', 'width', 12, '06/17/2006 02:45:15 PM')
insert into machine_settings values ('1', 'width', 7, '08/11/2006 10:04:15 AM')
insert into machine_settings values ('1', 'extension', 24, '07/08/2006 08:04:15 PM')
insert into machine_settings values ('1', 'diameter', 36, '08/11/2006 10:04:15 AM')
insert into machine_settings values ('2', 'diameter', 18, '08/10/2006 08:04:15 PM')
insert into machine_settings values ('2', 'extension', 12, '08/08/2006 08:04:15 PM')
Results should look like this:
machine_id setting_type_id setting_value change_date
1 width 7 08/11/2006 10:04:15 AM
1 extension 24 07/08/2006 08:04:15 PM
1 diameter 36 08/11/2006 10:04:15 AM
2 width 12 06/17/2006 02:45:15 PM
2 extension 12 08/10/2006 08:04:15 PM
2 diameter 36 08/10/2006 08:04:15 PM
Note that I need to display the data only from the latest record of each machine and setting.
I've gone around and around with this for a while now, with no love. It seems that all I'm coming up with are poor solutions involving temp tables, etc. Maybe it's just my day for a brain fart.
Anyway, your help will be greatly appreciated.
Thanks!
Steve
Steve
August 11, 2006 at 2:41 pm
By the way, I changed my example to use a table variable as opposed to a static table (for my own testing ease).
declare @machine_settings table (machine_id int, setting_type_id varchar(25), setting_value int, change_date datetime)
set nocount on
insert into @machine_settings values ('1', 'width', 5, '12/17/2005 10:45:15 AM')
insert into @machine_settings values ('2', 'width', 12, '06/17/2006 02:45:15 PM')
insert into @machine_settings values ('1', 'width', 7, '08/11/2006 10:04:15 AM')
insert into @machine_settings values ('1', 'extension', 24, '07/08/2006 08:04:15 PM')
insert into @machine_settings values ('1', 'diameter', 36, '08/11/2006 10:04:15 AM')
insert into @machine_settings values ('2', 'diameter', 18, '08/10/2006 08:04:15 PM')
insert into @machine_settings values ('2', 'extension', 12, '08/08/2006 08:04:15 PM')
select ms1.machine_id,
ms1.setting_type_id,
ms1.setting_value,
ms1.change_date
from @machine_settings ms1
inner join (select machine_id, setting_type_id, Max(change_date) as change_date
from @machine_settings
group by machine_id, setting_type_id) ms2
on ms1.machine_id = ms2.machine_id and ms1.setting_type_id = ms2.setting_type_id and ms1.change_date = ms2.change_date
order by ms1.machine_id
August 11, 2006 at 3:07 pm
Ah ha, now I see what I was forgetting. Thanks very much for your help.
Steve
August 13, 2006 at 8:41 pm
Ummm..... that's a bit wierd if you try following the signature path of this one...
--Jeff Moden
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply