Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2006
    Posts
    1

    Unanswered: help with a group by query

    Hi Could somebody please help

    I have the below query:
    select frf.EDGE_RECURRENCE_KEY, min(td.sql_date)
    from future_revenue_fact frf, EMBEDDED_EDGE_REV_ITEMS eeri, attribution_dimension ad, attribution_units_fact au, time_dimension td
    where frf.ATTRIBUTION_TRANSACTION_KEY = ad.ATTRIBUTION_TRANSACTION_KEY
    and ad.ATTRIBUTION_ROLE = 'Salesperson'
    and ad.ATTR_UNIT_TRANSACTION_KEY = au.ATTR_UNIT_TRANSACTION_KEY
    and frf.EDGE_RECURRENCE_KEY = eeri.EMBEDDED_EDGE_ID
    and eeri.EMBEDDED_EDGE_VERSION_NO = 0
    and frf.REVENUE_RECORD_TIME_KEY = td.TIME_KEY
    and frf.REVENUE_TYPE = 'Embedded Edge'
    and au.ATTRIBUTION_UNIT_NAME = 'Darren Starr'
    group by frf.EDGE_RECURRENCE_KEY

    This query works fine, however I need to somehow just return min(td.sql_date) in the select statement and not frf.EDGE_RECURRENCE_KEY as the min(td.sql_date) needs to feed as into another query eg:
    select *
    from table x
    where sql_date in ----> here i need to return the min(sql_date) using the first query.

    Is there anyway around this, besides using a stored proc??

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, there is any easy way: remove frf.EDGE_RECURRENCE_KEY from both the SELECT and the GROUP BY (i.e. remove the GROUP BY completely)

    which table is table x? are there any other tables besides table x in the outer query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Or, if you still want the minimum (now in the subquery) to refer to only the rows with an identical frf.EDGE_RECURRENCE_KEY, remove the GROUP BY, but add a correlated WHERE condition:
    ... AND frf.EDGE_RECURRENCE_KEY = corr.EDGE_RECURRENCE_KEY
    where "corr" would be the table alias name for future_revenue_fact in the outer query.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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