Select timestamp question

Databases
1

Hey all, im trying to get the select to work how I need it to, but im drawing a blank…



[B][COLOR=#0000ff]SELECT[/COLOR][/B] [COLOR=#ff00ff]company[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]id[/COLOR][COLOR=#0000ff],[/COLOR] [COLOR=#ff00ff]company[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]tech_id[/COLOR][COLOR=#0000ff],[/COLOR] [COLOR=#ff00ff]techs[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]name[/COLOR][COLOR=#0000ff],[/COLOR] [COLOR=#ff00ff]servers[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]last_report_date[/COLOR][COLOR=#0000ff],[/COLOR] [COLOR=#ff00ff]servers[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]server_name[/COLOR][B][COLOR=#0000ff]FROM[/COLOR][/B] [COLOR=#ff00ff]company[/COLOR] [B][COLOR=#0000ff]join[/COLOR][/B] [COLOR=#ff00ff]techs[/COLOR] [B][COLOR=#0000ff]on[/COLOR][/B] [COLOR=#ff00ff]techs[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]id[/COLOR] [COLOR=#0000ff]=[/COLOR] [COLOR=#ff00ff]company[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]tech_id[/COLOR] [B][COLOR=#0000ff]join[/COLOR][/B] [COLOR=#ff00ff]servers[/COLOR] [B][COLOR=#0000ff]on[/COLOR][/B] [COLOR=#ff00ff]servers[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]company_id[/COLOR] [COLOR=#0000ff]=[/COLOR] [COLOR=#ff00ff]company[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]id[/COLOR]

EG: If I have 4 companies, each company has 6 servers, I need to ONLY list the servers.last_report_date from the latest one from each company.

Company A -> last report ran on 10/16/2011
Company B -> last report ran on 10/12/2011



[B][COLOR=#0000ff]CREATE[/COLOR][/B] [B][COLOR=#0000ff]TABLE[/COLOR][/B] [COLOR=#808000]`company`[/COLOR] [COLOR=#0000ff]([/COLOR][COLOR=#808000]`id`[/COLOR] [B][COLOR=#800000]INT[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]10[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NOT[/COLOR][/B] [B][COLOR=#0000ff]NULL[/COLOR][/B] [COLOR=#0000ff][B]AUTO_INCREMENT[/B],[/COLOR][COLOR=#808000]`tech_id`[/COLOR] [B][COLOR=#800000]INT[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]10[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NOT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR][COLOR=#808000]`user_id`[/COLOR] [B][COLOR=#800000]INT[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]10[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NULL[/COLOR][/B] [B][COLOR=#0000ff]DEFAULT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR][COLOR=#808000]`company_name`[/COLOR] [B][COLOR=#800000]VARCHAR[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]50[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NOT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR][COLOR=#808000]`notes`[/COLOR] [B][COLOR=#800000]TEXT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR]	[B][COLOR=#0000ff]PRIMARY[/COLOR][/B] [B][COLOR=#0000ff]KEY[/COLOR][/B] [COLOR=#0000ff]([/COLOR][COLOR=#808000]`id`[/COLOR][COLOR=#0000ff])[/COLOR]



[B][COLOR=#0000ff]CREATE[/COLOR][/B] [B][COLOR=#0000ff]TABLE[/COLOR][/B] [COLOR=#808000]`techs`[/COLOR] [COLOR=#0000ff]([/COLOR][COLOR=#808000]`id`[/COLOR] [B][COLOR=#800000]INT[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]10[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NOT[/COLOR][/B] [B][COLOR=#0000ff]NULL[/COLOR][/B] [COLOR=#0000ff][B]AUTO_INCREMENT[/B],[/COLOR][COLOR=#808000]`username`[/COLOR] [B][COLOR=#800000]VARCHAR[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]30[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NULL[/COLOR][/B] [B][COLOR=#0000ff]DEFAULT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR][COLOR=#808000]`password`[/COLOR] [B][COLOR=#800000]VARCHAR[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]128[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NULL[/COLOR][/B] [B][COLOR=#0000ff]DEFAULT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR][COLOR=#808000]`name`[/COLOR] [B][COLOR=#800000]CHAR[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]30[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NULL[/COLOR][/B] [B][COLOR=#0000ff]DEFAULT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR][COLOR=#808000]`active`[/COLOR] [B][COLOR=#800000]INT[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]10[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NULL[/COLOR][/B] [B][COLOR=#0000ff]DEFAULT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR][COLOR=#808000]`logincount`[/COLOR] [B][COLOR=#800000]INT[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]10[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NULL[/COLOR][/B] [B][COLOR=#0000ff]DEFAULT[/COLOR][/B] [COLOR=#008000]'0'[/COLOR][COLOR=#0000ff],[B]PRIMARY[/B][/COLOR] [B][COLOR=#0000ff]KEY[/COLOR][/B] [COLOR=#0000ff]([/COLOR][COLOR=#808000]`id`[/COLOR][COLOR=#0000ff]))[/COLOR]



[B][COLOR=#0000ff]CREATE[/COLOR][/B] [B][COLOR=#0000ff]TABLE[/COLOR][/B] [COLOR=#808000]`servers`[/COLOR] [COLOR=#0000ff]([/COLOR][COLOR=#808000]`id`[/COLOR] [B][COLOR=#800000]INT[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]10[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NOT[/COLOR][/B] [B][COLOR=#0000ff]NULL[/COLOR][/B] [COLOR=#0000ff][B]AUTO_INCREMENT[/B],[/COLOR][COLOR=#808000]`company_id`[/COLOR] [B][COLOR=#800000]INT[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]10[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NOT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR][COLOR=#808000]`tech_id`[/COLOR] [B][COLOR=#800000]INT[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]10[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NOT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR][COLOR=#808000]`last_report_date`[/COLOR] [B][COLOR=#800000]TIMESTAMP[/COLOR][/B] [B][COLOR=#0000ff]NOT[/COLOR][/B] [B][COLOR=#0000ff]NULL[/COLOR][/B] [B][COLOR=#0000ff]DEFAULT[/COLOR][/B] [B][COLOR=#000080]CURRENT_TIMESTAMP[/COLOR][/B] [B][COLOR=#0000ff]ON[/COLOR][/B] [B][COLOR=#0000ff]UPDATE[/COLOR][/B] [B][COLOR=#000080]CURRENT_TIMESTAMP[/COLOR][/B][COLOR=#0000ff],[/COLOR][COLOR=#808000]`server_name`[/COLOR] [B][COLOR=#800000]VARCHAR[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]50[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NOT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR][COLOR=#808000]`os`[/COLOR] [B][COLOR=#800000]INT[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]10[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NULL[/COLOR][/B] [B][COLOR=#0000ff]DEFAULT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR][COLOR=#808000]`memory`[/COLOR] [B][COLOR=#800000]CHAR[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]10[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NULL[/COLOR][/B] [B][COLOR=#0000ff]DEFAULT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR][COLOR=#808000]`make`[/COLOR] [B][COLOR=#800000]CHAR[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]10[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NULL[/COLOR][/B] [B][COLOR=#0000ff]DEFAULT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR][COLOR=#808000]`model`[/COLOR] [B][COLOR=#800000]CHAR[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]20[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NULL[/COLOR][/B] [B][COLOR=#0000ff]DEFAULT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR][COLOR=#808000]`service_tag`[/COLOR] [B][COLOR=#800000]VARCHAR[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]20[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NULL[/COLOR][/B] [B][COLOR=#0000ff]DEFAULT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[/COLOR][COLOR=#808000]`ip_address`[/COLOR] [B][COLOR=#800000]CHAR[/COLOR][/B][COLOR=#0000ff]([/COLOR][COLOR=#800080]10[/COLOR][COLOR=#0000ff])[/COLOR] [B][COLOR=#0000ff]NOT[/COLOR][/B] [COLOR=#0000ff][B]NULL[/B],[B]PRIMARY[/B][/COLOR] [B][COLOR=#0000ff]KEY[/COLOR][/B] [COLOR=#0000ff]([/COLOR][COLOR=#808000]`id`[/COLOR][COLOR=#0000ff]))[/COLOR]
2

ive tried using MAX:

 [B][COLOR=#0000ff]SELECT[/COLOR][/B] [COLOR=#ff00ff]company[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]id[/COLOR][COLOR=#0000ff],[/COLOR] [COLOR=#ff00ff]company[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]tech_id[/COLOR][COLOR=#0000ff],[/COLOR] [COLOR=#ff00ff]techs[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]name[/COLOR][COLOR=#0000ff],[/COLOR] MAX([COLOR=#ff00ff]servers[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]last_report_date)[/COLOR][COLOR=#0000ff],[/COLOR] [COLOR=#ff00ff]servers[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]server_name[/COLOR][B][COLOR=#0000ff]FROM[/COLOR][/B] [COLOR=#ff00ff]company[/COLOR] [B][COLOR=#0000ff]join[/COLOR][/B] [COLOR=#ff00ff]techs[/COLOR] [B][COLOR=#0000ff]on[/COLOR][/B] [COLOR=#ff00ff]techs[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]id[/COLOR] [COLOR=#0000ff]=[/COLOR] [COLOR=#ff00ff]company[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]tech_id[/COLOR] [B][COLOR=#0000ff]join[/COLOR][/B] [COLOR=#ff00ff]servers[/COLOR] [B][COLOR=#0000ff]on[/COLOR][/B] [COLOR=#ff00ff]servers[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]company_id[/COLOR] [COLOR=#0000ff]=[/COLOR] [COLOR=#ff00ff]company[/COLOR][COLOR=#0000ff].[/COLOR][COLOR=#808000]id[/COLOR]

but that only returns one result

3 
SELECT company.id
     , company.tech_id
     , techs.name
     , servers.last_report_date
     , servers.server_name
  FROM company 
INNER  
  JOIN techs 
    ON techs.id = company.tech_id 
INNER
  JOIN ( SELECT company_id
              , MAX(last_report_date) AS  max_last_date
           FROM servers
         GROUP
             BY company_id ) AS x
    ON x.company_id = company.id
INNER                   
  JOIN servers 
    ON servers.company_id = x.company_id
   AND servers.last_report_date = x.max_last_date
4

Thanks Rudy, worked like a champ!