Results 1 to 2 of 2
  1. #1
    Join Date
    May 2012
    Posts
    3

    Unanswered: select max value of a group

    **I have this result:**

    CIUSMD ABISMD FAMSMI SFASMI AGLSMI TPRDPR DUADPR DTPDPR PBCDPR DSVDPR PDADPR
    2087492 CALICIDA 3 1 0 PA 20120507 20120507 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PD 20100527 0 1.88 0.0 22.22
    2087492 CALICIDA 3 1 0 PD 20101102 20101027 1.94 0.0 22.22
    2087492 CALICIDA 3 1 0 PF 20120208 20101006 1.83 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120131 20120103 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120131 20120105 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120131 20120108 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120131 20120111 2.21 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120131 20120127 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120204 20120203 2.21 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120214 20120214 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120405 20120405 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120411 20120410 2.27 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120412 20120412 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120413 20120413 2.27 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120417 20120416 2.27 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120419 20120418 2.27 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120423 20120423 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120425 20120425 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120427 20120426 2.27 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120507 20120507 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120510 20120510 2.27 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120514 20120514 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120516 20120515 2.27 0.0 0.00
    2087492 CALICIDA 3 1 0 PF 20120519 20120521 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20100630 20100630 1.78 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20101001 20101001 1.83 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120208 20101006 1.83 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20101007 20101007 1.78 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20101019 20101019 1.83 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20101110 20101021 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20101029 20101029 1.83 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20101116 20101116 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110209 20110209 1.88 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110214 20110214 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110220 20110220 1.97 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110223 20110223 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110304 20110304 1.97 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110309 20110309 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110324 20110324 1.97 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110325 20110325 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110405 20110405 1.78 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110412 20110412 1.97 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110427 20110427 1.88 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110502 20110502 1.97 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110504 20110504 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110505 20110505 1.97 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110518 20110518 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110604 20110604 1.97 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110625 20110625 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110706 20110705 1.97 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110801 20110801 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110808 20110808 1.97 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110822 20110822 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110824 20110824 1.97 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20110928 20110927 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20111121 20111121 1.97 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20111128 20111128 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120131 20120103 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120131 20120105 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120131 20120111 2.21 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120131 20120127 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120204 20120203 2.21 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120214 20120214 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120405 20120405 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120411 20120410 2.27 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120412 20120412 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120413 20120413 2.27 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120417 20120416 2.27 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120419 20120418 2.27 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120423 20120423 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120425 20120425 2.14 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120427 20120426 2.27 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120507 20120507 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120510 20120510 2.27 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120514 20120514 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120516 20120515 2.27 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120519 20120521 1.89 0.0 0.00

    **Desired result:**

    CIUSMD ABISMD FAMSMI SFASMI AGLSMI TPRDPR DUADPR DTPDPR PBCDPR DSVDPR PDADPR
    2087492 CALICIDA 3 1 0 PA 20120507 20120507 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PD 20101102 20101027 1.94 0.0 22.22
    2087492 CALICIDA 3 1 0 PF 20120519 20120521 1.89 0.0 0.00
    2087492 CALICIDA 3 1 0 PG 20120519 20120521 1.89 0.0 0.00

    select c.CIUSMD, c.ABISMD, a.famsmi, a.sfasmi, a.aglsmi, b.TPRDPR, b.DUADPR, b.DTPDPR, b.PBCDPR, b.DSVDPR, b.pdadpr
    from swapfilio.gesmi a join swapfilio.gefpr b
    on a.socsmi = b.socdpr
    and a.niismi = b.artdpr
    and a.embsmi = b.embdpr
    and a.gfmsmi = b.gfmdpr
    join swapfilio.gesmd c
    on b.socdpr = c.SOCSMD
    and b.artdpr = c.NIISMD
    where b.TPRDPR in ('PA', 'PD', 'PF', 'PG', 'PV', 'PI')
    and b.DTPDPR <= '20120529'
    and ciusmd = '2087492'

    Notice that the field ciusmd is not going to be in the where clause (i put it there to give me less results in my query).
    So i what the max date per ciusmd and tprdpr.

    Thank you

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

Posting Permissions

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