trouble with select max(col-nm), pls help.....

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

trouble with select max(col-nm), pls help.....

Author

Topic

saltytom
Starting Member

6 Posts

Posted - 2008-02-19 : 14:09:22

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

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-19 : 15:25:57

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
               )

saltytom
Starting Member

6 Posts

Posted - 2008-02-19 : 15:53:37

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.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-19 : 16:28:37

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

saltytom
Starting Member

6 Posts

Posted - 2008-02-21 : 17:39:11

Thanks Jdaman... it's close but not quite there, might have to update this..... thanks so much again

saltytom
Starting Member

6 Posts

Posted - 2008-02-22 : 08:34:02

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources