Date:         Wed, 25 Feb 1998 09:12:51 +0500
Reply-To:     Bernard Tremblay <bernard@CAPITALE.QC.CA>
Sender:       "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From:         Bernard Tremblay <bernard@CAPITALE.QC.CA>
Subject:      Re: SQL Puzzle - How to get latest detail records for each circuit
Comments: To: bgwillia@VCU.EDU

Hi,
 
        You need an having clause to keep only the observation with
the maximum date.
 
        ADD:
                having date = max(date)
 
        to your request and it should work out ok.
 
                Regards,
                        Bernard Tremblay
          \\\|///
        \\  - -  //
         (  @ @  )
+------oOOo-(_)-oOOo----------+---------------------------------+
|  Bernard Tremblay           |                                 |
|             La Capitale     | Tel:  (418) 646-2401            |
|                             | Fax:  (418) 646-5960            |
|                             | Int:  bernard@capitale.qc.ca    |
+-----------------------------+---------------------------------+
|             Imaginasys enr  | Res:  (418) 878-4447            |
|                             | Int:  bertrem@quebectel.com     |
+---------------Oooo----------+---------------------------------+
        oooO   (   )
       (   )    ) /
        \ (    (_/
         \_)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 
 
>>>From owner-sas-l@UGA.CC.UGA.EDU Tue Feb 24 23:58 EST 1998
>>>Nntp-Posting-Host: 128.172.3.18
>>>X-Newsreader: Forte Free Agent 1.11/32.235
>>>Xref: paladin.american.edu comp.soft-sys.sas:47283
>>>Date:         Tue, 24 Feb 1998 22:31:30 GMT
>>>Sender: "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
>>>From: "Boyce G. Williams, Jr." <bgwillia@VCU.EDU>
>>>Subject:      SQL Puzzle - How to get latest detail records for each circuit
>>>To: SAS-L@UGA.CC.UGA.EDU
>>>Content-Type: text
>>>Content-Length: 1777
>>>
>>>Hi y'all,
>>>
>>>I've got a SQL puzzle that's just got me stumped.  This is part of a
>>>table CIRCUITS I have:
>>>
>>>    circuit          date       dept
>>>    48CSNA2922  6/22/96 2236050
>>>    48CSNA2922  6/23/96 2236051
>>>    48CSNA2922  6/24/96 2236052
>>>    48CSNA2922  6/25/96 2236053
>>>    48CSNA2982  6/21/96 2236053
>>>    48CSNA2982  6/22/96 2236052
>>>    48CSNA2982  6/23/96 2236051
>>>    48CSNA2982  6/24/96 2236050
>>>    48CSNA3007  6/25/96 2236053
>>>    48DHDA2668  6/23/96 0206751
>>>    48DHDA2668  6/24/96 0206750
>>>    48DHDA2668  6/25/96 0206752
>>>    48DHDA2933  6/21/96 0206750
>>>    48DHDA2933  6/22/96 0206751
>>>    48DHDA2933  6/25/96 0206750
>>>    48DHDA2962  6/21/96 2236360
>>>
>>>Now what I need is, through SQL coding, a list of these records, but
>>>only from those containing the latest date:
>>>
>>>    circuit          date       dept
>>>    48CSNA2922  6/25/96 2236053
>>>    48CSNA2982  6/24/96 2236050
>>>    48CSNA3007  6/25/96 2236053
>>>    48DHDA2668  6/25/96 0206752
>>>    48DHDA2933  6/25/96 0206750
>>>    48DHDA2962  6/21/96 2236360
>>>
>>>The closest I've gotten is:
>>>
>>>SELECT DISTINCT circuit, Max(date) AS MaxOfdate
>>>FROM CIRCUITS
>>>GROUP BY circuit;
>>>
>>>which will give me the first two columns I need, but how to acquuire
>>>the dept associated with the first columns?
>>>
>>>Any help will be welcomed
>>>
>>>Boyce G. Williams, Jr.
>>>
>>> .--------------------------------------------------------------------.
>>> | "People should have two virtues:  purpose- the courage to envisage |
>>> | and pursue valued goals uninhibited by the defeat of infantile     |
>>> | fantasies, by guilt and the failing fear punishment;  and wisdom- a|
>>> | detached concern with life itself, in the face of death itself."   |
>>> |                                                     Norman F. Dixon|
>>> '--------------------------------------------------------------------'
>>>