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

    Unanswered: dates format, sorting and duplicates

    Hello I am a SQL and DB2 newbie, so sorry if I ask something obvious but I could not solve it by myself.

    I have the query:

    Code:
    SELECT CAST(SYB.date_to_char1(a.date, 1) AS CHAR(12))
    FROM
       (SELECT DISTINCT call_date as date FROM x ORDER BY call_date DESC) a
    ORDER BY a.date desc;
    which returns:

    Code:
    04/09/2012  
    04/03/2012  
    03/26/2012  
    03/25/2012  
    03/24/2012  
    03/23/2012  
    03/22/2012  
    03/22/2012  
    03/21/2012  
    03/28/2010  
    03/27/2010  
    03/26/2010  
    03/26/2010  
    03/25/2010  
    03/25/2010  
    03/24/2010  
    03/23/2010
    call_date format is:

    Code:
    2012-04-09 15:24:29
    2012-04-03 15:28:31
    2012-03-26 15:49:14
    2012-03-25 15:42:05
    2012-03-24 15:42:04
    2012-03-23 15:42:04
    2012-03-22 15:42:05
    2012-03-22 13:59:36
    ... etc
    I would like the same result but without duplicates, but if I use DISTINCT in the first select I get the error:

    42882 The specific instance name qualifier is not equal to the function name qualifier.

    If I try:

    Code:
    SELECT  DISTINCT CAST(SYB.date_to_char1(call_date, 1) AS CHAR(12)) AS date 
    FROM x
    ORDER BY date DESC;
    I get:

    Code:
    04/09/2012  
    04/03/2012  
    03/28/2010  
    03/27/2010  
    03/26/2012  
    03/26/2010  
    03/25/2012  
    03/25/2010  
    03/24/2012  
    03/24/2010  
    03/23/2012  
    03/23/2010  
    03/22/2012  
    03/21/2012
    Which is not correctly sorted (it's sorted as strings instead of dates)


    Can you give me some clues?

    Thanks in advance for your help!

  2. #2
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Suggest not ordering by a CHAR data type if you want the result in date order?

    Possibly i misunderstand something?

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    OuFeRRaT, the problem is the values in CALL_DATE are not dates but Timestamps (it is a good idea to name columns to indicate this).

    When you run the nested table to get disticnt CALL_DATE, both of these rows are kept because they are not duplicate timestamp values.

    2012-03-22 15:42:05
    2012-03-22 13:59:36

    You might try:
    Code:
    SELECT DISTINCT CHAR(DATE(CALL_DATE), USA)
    FROM X
    ORDER BY call_date DESC

  4. #4
    Join Date
    May 2012
    Posts
    3
    Thanks for your answers!

    Stealth_DBA your solution gives me an error:

    Code:
    42822	An expression in the ORDER BY clause or GROUP BY clause is not valid.
    I think this is because you cannot put a column that is not in select when using distinct.

    If I try:

    Code:
    SELECT DISTINCT CHAR(DATE(CALL_DATE), USA), call_date
    FROM tel_rep_stats
    ORDER BY call_date DESC
    The result has duplicates and two columns.

    Can you suggest something else?
    Thanks!!

  5. #5
    Join Date
    May 2012
    Posts
    3
    Ok, I finally got what I wanted:

    Code:
    SELECT DISTINCT a.date
    FROM
    (
    SELECT DATE(CALL_DATE) as date, COUNT(DISTINCT 1) 
    FROM tel_rep_stats
    GROUP BY call_date
    ) a
    ORDER BY a.date DESC
    ;
    result:

    Code:
    2012-04-09
    2012-04-03
    2012-03-26
    2012-03-25
    2012-03-24
    2012-03-23
    2012-03-22
    2012-03-21
    2010-03-29
    2010-03-28
    2010-03-27
    2010-03-26
    2010-03-25
    2010-03-24
    Do you think there are easier ways to obtain the same result?
    Thanks a lot!

  6. #6
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    OuFeRRaT, That is what I don't have time to test something out. This should work:
    Code:
    WITH TEST_TAB (COL_TSMP)
      AS (
          SELECT CURRENT TIMESTAMP           FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP - 1 HOUR  FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP + 1 HOUR  FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP - 1 MONTH FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP + 1 MONTH FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP - 1 DAY   FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP + 1 DAY   FROM SYSIBM.SYSDUMMY1
         )
    SELECT DISTINCT CHAR(DATE(COL_TSMP), USA) AS COL_DATE
    FROM TEST_TAB
    ORDER BY COL_DATE DESC
    
    COL_DATE  
    ----------
    06/18/2012
    05/19/2012
    05/18/2012
    05/17/2012
    04/18/2012

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If col_tsmp spanned more than one year, the order might not you wanted,
    like...
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH TEST_TAB (COL_TSMP)
      AS (
          SELECT CURRENT TIMESTAMP           FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP - 1 HOUR  FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP + 1 HOUR  FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP - 1 MONTH FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP + 1 MONTH FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP - 1 DAY   FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP + 1 DAY   FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP - 1 YEAR  FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP + 1 YEAR  FROM SYSIBM.SYSDUMMY1
         )
    SELECT DISTINCT CHAR(DATE(COL_TSMP), USA) AS COL_DATE
    FROM TEST_TAB
    ORDER BY COL_DATE DESC;
    ------------------------------------------------------------------------------
    
    COL_DATE  
    ----------
    06/18/2012
    05/19/2012
    05/18/2013
    05/18/2012
    05/18/2011
    05/17/2012
    04/18/2012
    
      7 record(s) selected.
    In that case, this might be better.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH TEST_TAB (COL_TSMP)
      AS (
          SELECT CURRENT TIMESTAMP           FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP - 1 HOUR  FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP + 1 HOUR  FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP - 1 MONTH FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP + 1 MONTH FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP - 1 DAY   FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP + 1 DAY   FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP - 1 YEAR  FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP + 1 YEAR  FROM SYSIBM.SYSDUMMY1
         )
    SELECT CHAR(DATE(COL_TSMP), USA) AS COL_DATE
    FROM TEST_TAB
    GROUP BY
          DATE(COL_TSMP)
    ORDER BY
          DATE(COL_TSMP) DESC;
    ------------------------------------------------------------------------------
    
    COL_DATE  
    ----------
    05/18/2013
    06/18/2012
    05/19/2012
    05/18/2012
    05/17/2012
    04/18/2012
    05/18/2011
    
      7 record(s) selected.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you want to use DISTINCT instead of GROUP BY,
    this might be an answer.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH TEST_TAB (COL_TSMP)
      AS (
          SELECT CURRENT TIMESTAMP           FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP - 1 HOUR  FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP + 1 HOUR  FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP - 1 MONTH FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP + 1 MONTH FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP - 1 DAY   FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP + 1 DAY   FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP - 1 YEAR  FROM SYSIBM.SYSDUMMY1 UNION ALL
          SELECT CURRENT TIMESTAMP + 1 YEAR  FROM SYSIBM.SYSDUMMY1
         )
    SELECT CHAR(COL_DATE, USA) AS COL_DATE
     FROM (SELECT DISTINCT DATE(COL_TSMP) AS COL_DATE
            FROM TEST_TAB
          ) t
    ORDER BY
          t.COL_DATE DESC;
    ------------------------------------------------------------------------------
    
    COL_DATE  
    ----------
    05/18/2013
    06/18/2012
    05/19/2012
    05/18/2012
    05/17/2012
    04/18/2012
    05/18/2011
    
      7 record(s) selected.

Posting Permissions

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