Results 1 to 6 of 6
  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
    Aug 2001
    Location
    UK
    Posts
    4,650
    Can you also say what columns make the group and which column's max you want.

    It is good that you have provided sample data, but will be useful to have a verbose description as well.

    Do you have a DB2 version and platform ?

    is this a class assignment ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    May 2012
    Posts
    3

    select max value of a group

    i just want the max for the field b.DTPDPR
    the group by should be c.CIUSMD, c.ABISMD, a.famsmi, a.sfasmi, a.aglsmi, b.TPRDPR

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Is your question same as in MySQL forum's one?

    Quote Originally Posted by hugoscp View Post
    **I have this result:**
    ...
    ...
    So i what the max date per ciusmd and tprdpr.

    Thank you

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by tonkuma View Post
    Is your question same as in MySQL forum's one?
    he wants it solved elsewhere...

    Select max value of a group - Dev Shed
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    So, I want to see by which way the issue would be solved by MySQL or other DBMS.
    Because, it is too simple(and not intersting) for me to solve the issue on DB2.
    It may be solved by using an OLAP specificatin.

Posting Permissions

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