Multi-column grouping help requested

• sarmistead

  SSCommitted

  Points: 1671

  More actions

  August 11, 2006 at 2:22 pm

  #114082

  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_idsetting_type_idsetting_valuechange_date

  1width708/11/2006 10:04:15 AM

  1extension2407/08/2006 08:04:15 PM

  1diameter3608/11/2006 10:04:15 AM

  2width1206/17/2006 02:45:15 PM

  2extension1208/10/2006 08:04:15 PM

  2diameter3608/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

• John Rowan

  SSC Guru

  Points: 56440

  More actions

  August 11, 2006 at 2:41 pm

  #654668

  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

  John Rowan

  ======================================================
  ======================================================
  Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

• sarmistead

  SSCommitted

  Points: 1671

  More actions

  August 11, 2006 at 3:07 pm

  #654676

  Ah ha, now I see what I was forgetting. Thanks very much for your help.

  Steve

• Jeff Moden

  SSC Guru

  Points: 1004552

  More actions

  August 13, 2006 at 8:41 pm

  #654729

  Ummm..... that's a bit wierd if you try following the signature path of this one...

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)