Problem with query - eliminating a row

Giganews Newsgroups
Subject: Problem with query - eliminating a row
Posted by:  Henry (greencl…@optonline.net)
Date: Tue, 19 Apr 2011

I'm using SQL Server 2005 to return information from legal records.

Maybe I just had a long day, but I had problems eliminating a row, as
shown in the data below.
A colleague, who is building a Business Objects universe and reports,
came to me with this problem.
I've changed table and column names in order to preserve confidentiality.

I changed the original query to implement a CTE, thinking that I could
then select the MAX date and thus eliminate all but the latest date,
but as you can see, this doesn't work.

I know that this can be cleaned up quite a bit, but, what can I say, I
figured I'd take the easy way out and ask for some community help.
I've indicated in the sample data (following the query) what I want to
eliminate.  I'd be very grateful if anyone could give me a push in the
right direction.

WITH cteTable1 As
    (SELECT
      CDD_Office.name,
      ca.desk_number,
      e.name customer_name,
      ca.start_date
    FROM
        Case ca LEFT OUTER JOIN Court
        ON (ca.court_key = Court.court_key)
        RIGHT OUTER JOIN marbles m
        ON (ca.motions_key = m.motions_key
        AND  m.group_key = 203)
        INNER JOIN Entity e
        ON (m.client_key = e.entity_key
        AND  m.group_key = 203)
        INNER JOIN OtherDB.dbo.OCAM o
        ON (m.group_key = 203
          AND o.office_key in (8,9,10,11,53)
          AND o.assigned_key = m.assigned_key)
        LEFT OUTER JOIN AnotherArea aa
        ON ((ca.action_1_key = aa.action_key)
        OR (ca.action_2_key = aa.action_key)
        OR (ca.action_3_key = aa.action_key))
    WHERE
      (
        ((o.Office = 'Brown Office - Complex A')
        OR ( o.Office + ' ' + '-->>' + ' ' + ( ' -All-' ) = 'Brown
Office - Complex A')
      )
        AND
        ( (ca.start_date >= '01/03/2011')
          and (ca.start_date < '01/09/2011') )
        AND
        (aa.description = 'POGO')
    )
    GROUP BY o.[name],
      ca.docket_indict_number,
      Entity9.name,
      ca.start_date
    )
SELECT MAX([name]) [name],
        MAX(desk_number) desk_number,
        MAX(customer_name) customer_name,
        max([start_date]) startdate
FROM  cteTable1
GROUP BY [start_date],
        [name],
        desk_number,
        customer_name
ORDER BY [name],
          customer_name, startdate desc

Name                trans_number  customer              startdate
Carter, Carl        2010BX072863  CARVER, JONATHAN      2011-01-04
00:00:00.000
Copeley, Jerry      2010BX024154  AMMONS, ALBERT        2011-01-06
00:00:00.000
Cunningham, Gail    2010BX071297  BENSON, JOSEPH        2011-01-06
00:00:00.000 <= Keep this row - this is the latest date
Cunningham, Gail    2010BX071297  BENSON, JOSEPH        2011-01-04
00:00:00.000 <= Eliminate this row - not the latest (MAX) date
Cunningham, Gail    2010BX063647  WESTERLY, DERRICK D  2011-01-03
00:00:00.000
Greenberg, Michael  2010BX064667  MALDONADO, DAVID      2011-01-04
00:00:00.000
Greenberg, Michael  2011BX001107  MANNING, JAMES        2011-01-07
00:00:00.000
Greenberg, Michael  2010BX017508  ROSE, ALETA          2011-01-07
00:00:00.000
Greenberg, Michael  2011BX001109  ROSE, ALETA          2011-01-07
00:00:00.000
Greenberg, Michael  2010BX070924  VASQUEZ, JOSEPH      2011-01-03
00:00:00.000
Greenberg, Michael  2010BX043518  WILLIAM, DENNIS      2011-01-04
00:00:00.000

Replies