Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2005
    Posts
    276

    Unanswered: getting data with max(src_ts)

    This query below gives the MAX(SRC_TS) from the PRCD_RUN table with FINISH status

    SELECT MAX (d.src_ts)
    FROM prcs_run b, TAB D
    WHERE d.prcs_run_id = b.prcs_run_id
    AND b.prcs_run_status = 'FINISH'
    AND d.CC_ID = '123'
    AND d.YEAR = 200901
    AND D.MM_ID ='345'
    Of the 2 rows I have- its returns the second one
    2/10/2009 10:30:11 AM
    2/11/2009 6:15:37 PM --> this one


    But when I use it in the query, it returns both the rows with the different Timestamps.
    I want this query to return only one row with max(src_ts) which is 2/11/2009 6:15:37 PM.

    SELECT * FROM tab D
    WHERE CC_ID = '123'
    AND YEAR = 200901 AND MM_ID ='345'
    AND SRC_TS = (SELECT MAX (d.src_ts)
    FROM prcs_run b
    WHERE d.prcs_run_id = b.prcs_run_id
    AND b.prcs_run_status = 'FINISH')
    Any idea where I'm going wrong with this.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >AND d.YEAR = 200901
    VERY strange looking "year" if you ask me.

    >Any idea where I'm going wrong with this.
    Implicit data type conversion bites you again.

    Why not use the SQL which gives you the desired results?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Because of this, perhaps?

    AND SRC_TS = (SELECT MAX (d.src_ts)

    Shouldn't it be "b.src_ts"?

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Any idea where I'm going wrong with this.
    Strange usage of correlated subquery.
    You query table B in the subquery, but you pick column from table D into its SELECT clause. As it returns different values for different rows in table D, it picks maximal dates for all PRCS_RUN_ID values in table D (the join column).

    Use EXISTS clause with subquery on table B conditions (plus the join one) only instead.

  5. #5
    Join Date
    Jul 2005
    Posts
    276
    It works fine now. I included the tab d in the from clause in the sub query.

    SELECT * FROM tab D
    WHERE CC_ID = '123'
    AND YEAR = 200901 AND MM_ID ='345'
    AND SRC_TS = (SELECT MAX (d.src_ts)
    FROM prcs_run b, tab D
    WHERE d.prcs_run_id = b.prcs_run_id
    AND b.prcs_run_status = 'FINISH')

  6. #6
    Join Date
    Dec 2003
    Posts
    1,074
    Careful about having the same alias "D" in both the main query and the subquery. Honestly, I don't know which one "wins" when you code that way ...

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by nandinir
    It works fine now. I included the tab d in the from clause in the sub query.
    If you expect it to return zero rows, when the row in table D with maximal date has different CC_ID, YEAR or MM_ID, then it is surely fine.
    Otherwise you shall repeat those conditions (within the join) in both parts of the query.

    However, the query could be much simpler if using analytic SUM function:
    Code:
    SELECT *
    FROM (
      SELECT d.*, DENSE_RANK() OVER (ORDER BY src_ts DESC) rn
      FROM tab d
      WHERE CC_ID = '123'
        AND YEAR = 200901
        AND MM_ID ='345'
        AND EXISTS(
          SELECT 1
          FROM prcs_run b
          WHERE d.prcs_run_id = b.prcs_run_id
            AND b.prcs_run_status = 'FINISH'
        )
      )
    WHERE rn = 1;

Posting Permissions

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