Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    24

    Unanswered: how do I return the highest date per group

    I need to build a unit history table to track daily changes in an unit operations table. I want to compare a copy of the operations table to a result set containing the entries with the latest date for each unit from the history table, delete duplicates from the copy table and insert the remainder into the history table.

    My problem is the query returning the entries with the latest date per unit. I know how to return all units with the max date in the table but this misses any units that didn't change on the higest date in the table. This is resulting in duplicate entries every other day.

    This is the query so far. The problem is with the AS b part of the delete statement.

    >begin tran
    >
    >insert into mobile_changes
    >(Mobile, FID, RID, PIN, Commission, Barred, Barred_ACSE, LES)
    >
    >select Mobile, FID, RID, PIN, Commission, Barred, Barred_ACSE, LES
    >from A1MobList union all
    >select Mobile, FID, RID, PIN, Commission, Barred, Barred_ACSE, LES
    >from A2MobList union all
    >select Mobile, FID, RID, PIN, Commission, Barred, Barred_ACSE, LES
    >from R1MobList union all
    >select Mobile, FID, RID, PIN, Commission, Barred, Barred_ACSE, LES
    >from R2MobList
    >GO
    >
    >delete mobile_changes from mobile_changes as a,
    >(select * from mobile_history where date_time = (select max(date_time)
    >from mobile_history)) as b
    >where
    >a.mobile = b.mobile and
    >a.fid = b.fid and
    >a.rid = b.rid and
    >a.pin = b.pin and
    >a.commission = b.commission and
    >a.barred = b.barred and
    >a.barred_ACSE = b.barred_ACSE and
    >a.LES = b.LES
    >GO
    >
    >insert into mobile_history
    >(Mobile, FID, RID, PIN, Commission, Barred, Barred_ACSE, LES)
    >select
    >Mobile, FID, RID, PIN, Commission, Barred, Barred_ACSE, LES
    >from Mobile_Changes
    >GO
    >
    >delete from mobile_changes
    >GO
    >
    >commit tran

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: how do I return the highest date per group

    You need to get the max(date_time) per unit, and then use that to find the latest record for the unit:
    PHP Code:
    select *
    from xxx
    where 
    (unitdate_timein
    select unitmax(date_time)
      
    from xxx
      group by unit
    ); 
    Note that if (unit, date_time) are not unique in the table then you could get back more than 1 row per unit.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    not all databases support that structure, tony

    a correlated subquery is a good alternative, works even in Access:
    PHP Code:
    select *
      
    from xxx as t1
     where date_time 
    =
           ( 
    select max(date_time)
               
    from xxx
              where unit 
    t1.unit 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Oct 2003
    Posts
    24

    Re: how do I return the highest date per group

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •