SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 trouble with select max(col-nm), pls help.....
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

saltytom
Starting Member

USA
6 Posts

Posted - 02/19/2008 :  14:09:22  Show Profile  Reply with Quote
this is a list of vehicles & their last inspection dates. I just want 1 row returned for each vehicle, containing max(a.date_last_pref).
thanks, cheers....

--sql for finding the last inspection procedure done for revenue veh
select a.equipment_no, d.equipment_name,
max(a.date_last_perfd) 'date last perf', a.procedure_id,
c.procedure_name, e.product_family
from equip_procedures a,life_proc_status b, procedure_master c,
equipment_master d, item_master e
where a.equipment_no = b.equipment_no
and a.procedure_id = c.procedure_id
and a.equipment_no = d.equipment_no
and d.item_no = e.item_no
and date_last_perfd is not null
and e.product_family in ('1', '4', '5')
and (a.procedure_id like '%2059'or a.procedure_id like '%2069'
or a.procedure_id like '%2079' or a.procedure_id like '%2159'
or a.procedure_id like '%2409' or a.procedure_id like '%2429'
or a.procedure_id like '%2449' or a.procedure_id like '%2469'
or a.procedure_id like '%2509' or a.procedure_id like '%2559')
group by a.equipment_no, a.procedure_id, a.date_last_perfd,
c.procedure_name, d.equipment_name, e.product_family
order by a.equipment_no

RESULTS
equip#|equip_nm|dt last perf|proced id|proced nm|
0102 truck#102 2000-06-05 5350002069 AA inspec
0102 truck#102 2006-03-05 5350002159 C inspec
*I just want this row above for 0102 date is highest
0102 truck#102 2000-06-05 5350002079 A inspec
0102 truck#102 2003-02-09 5350002059 B inspec

0103 truck#103 2000-06-12 5350002079 A inspec
0103 truck#103 2007-03-25 5350002059 B inspec
0103 truck#103 2007-12-05 5350002159 C inspec
*I just want this row above for 0103 -date is highest
0103 truck#103 2005-06-12 5350002069 AA insp



Edited by - saltytom on 02/19/2008 14:57:56

jdaman
Constraint Violating Yak Guru

USA
354 Posts

Posted - 02/19/2008 :  15:25:57  Show Profile  Reply with Quote
See if you can use the following technique:
DECLARE @sample TABLE ( id INT, date DATETIME )
INSERT @sample ( id, date )
SELECT 1, '20080101' UNION
SELECT 1, '20080104' UNION
SELECT 1, '20080115' UNION
SELECT 2, '20080102' UNION
SELECT 2, '20080112' UNION
SELECT 2, '20080116'

SELECT  *
FROM    @sample s
WHERE   date = ( SELECT MAX(date)
                 FROM   @sample
                 WHERE  id = s.id
               )
Go to Top of Page

saltytom
Starting Member

USA
6 Posts

Posted - 02/19/2008 :  15:53:37  Show Profile  Reply with Quote
I dont understand how to structure the subquery 'where' clause without rewriting the query inside the subquery. Are you also saying for me to create a temp table to select from, and if so, I'm not following how the UNION would work. thanks.
Go to Top of Page

jdaman
Constraint Violating Yak Guru

USA
354 Posts

Posted - 02/19/2008 :  16:28:37  Show Profile  Reply with Quote
The @sample table is only for demonstration purposes. Here is how I would think you would lay it out in your example:
select  a.equipment_no,
        d.equipment_name,
        a.date_last_perfd,
        a.procedure_id,
        c.procedure_name,
        e.product_family
from    equip_procedures a,
        life_proc_status b,
        procedure_master c,
        equipment_master d,
        item_master e
where   a.equipment_no = b.equipment_no
        and a.procedure_id = c.procedure_id
        and a.equipment_no = d.equipment_no
        and d.item_no = e.item_no
        and date_last_perfd is not null
        and e.product_family in ( '1', '4', '5' )
        and ( a.procedure_id like '%2059'
              or a.procedure_id like '%2069'
              or a.procedure_id like '%2079'
              or a.procedure_id like '%2159'
              or a.procedure_id like '%2409'
              or a.procedure_id like '%2429'
              or a.procedure_id like '%2449'
              or a.procedure_id like '%2469'
              or a.procedure_id like '%2509'
              or a.procedure_id like '%2559'
            )
        and a.date_last_perfd = ( select    max(date_last_perfd)
                                  from      equip_procedures
                                  where     equipment_no = a.equipment_no
                                )
order by a.equipment_no
Go to Top of Page

saltytom
Starting Member

USA
6 Posts

Posted - 02/21/2008 :  17:39:11  Show Profile  Reply with Quote
Thanks Jdaman... it's close but not quite there, might have to update this..... thanks so much again
Go to Top of Page

saltytom
Starting Member

USA
6 Posts

Posted - 02/22/2008 :  08:34:02  Show Profile  Reply with Quote
I tried that sql and thought it would work but I get a thousand dups, and I tried select distinct too. how do I figure what the problem is, table by table, methodically? -what to try next?
this is the output with the latest sql from above

equip equip name last perf proced id proced name
0200 0200 truck 01/01/08 9500002509 c inspection
0200 0200 truck 01/01/08 9500002509 c inspection
0200 0200 truck 01/01/08 9500002509 c inspection
0331 0331 dodge 02/02/08 9500002449 d inspection
0331 0331 dodge 02/02/08 9500002449 d inspection

Edited by - saltytom on 02/22/2008 09:23:50
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000