Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2003
    Posts
    34

    Unanswered: Db2 gropu by/order by

    Data in the table.

    TYPE DATE
    ---- ----------
    ZZ 1999-01-01
    MF 2020-01-01
    IK 2014-01-01
    AC 2017-01-01
    AC 1980-01-01
    AB 2013-01-01
    AB 2001-01-01
    AA 1990-01-01
    AA 1980-01-01

    I want the above data in one of my tables to sorted and shown as below. It has to be sorted on the date first and then if there are more records for that type then I need to show that next as shown below. Can somebody help me in building the SQL to show the result as below. I tried using group by/order by but nothing is giving my result correctly.
    Appreciate your help
    .
    I am running DB2 V 10 in Z/OS

    Expected Output
    ----------------

    TYPE DATE
    ---- ----------
    MF 2020-01-01
    AC 2017-01-01
    AC 1980-01-01
    IK 2014-01-01
    AB 2013-01-01
    AB 2001-01-01
    ZZ 1999-01-01
    AA 1990-01-01
    AA 1980-01-01

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    If db2 v10 on zos support olap function then ,you can try this:
    Code:
    select type,dt
    from (
       select t.*
              ,max(dt) over (partition by type) as max_dt 
         from t19 t 
    ) order by max_dt desc, type, dt desc

  3. #3
    Join Date
    Jul 2003
    Posts
    34
    Fengsun2,
    Thanks for your query.

    Even though we are using DB2 V 10, we are not using the new function mode in DB2 V 10. We are still in conversion mode. But we will soon move to the function mode. I till try the SQL at that time.

    But meanwhile do you see any other method to acheive the same results.

    Thanks,
    Gopu

  4. #4
    Join Date
    Nov 2011
    Posts
    334
    If you can not use olap function ,then maybe we need a join to do this:
    Code:
    select a.type,a.dt
      from t19 a,
      ( select type, max(dt) as max_dt
          from t19
          group by type ) b
      where a.type=b.type
      order by max_dt desc, a.type, a.dt desc;

Tags for this Thread

Posting Permissions

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