Business Objects Query...

[pgeek]

I have a number of rows per record with different create dates and activity logs (both in seperate columns). How would I be able to only select the activity log with the latest create date?

Cheers.

[szlamany]

Welcome to the forum

Basically a query like this would accomplish what you want.

Select * From SomeTable T1
   Where T1.DateCreated=(Select Max(T2.DateCreated)
                                      From SomeTable T2
                                      Where T2.ActivityLog=T1.ActivityLog)

The sub-query pulls the latest date created - and the main query uses that in the where clause.

The where clause in the sub-query is needed to pull the DateCreated from the right set of associated rows.

If you gave us your actual table name and column names - and also told us the database you are using - we could be a lot more specific with our answers.

[pgeek]

Hi - thanks for the welcome. The existing SQL is as below if this helps? It's written with Business Objects, the problem is that I don't know how to pick out the lastest activity log as they are each returned on individual rows?

Thanks for the help!

SELECT
  TKT_TT_MAIN.TT_ID,
  STK_BOB_DATE(TKT_TT_MAIN.Ora_Fault_Start_Time),
  STK_BOB_DATE(TKT_TT_MAIN.Ora_Fault_Resolvee_Time),
  TKT_TT_MAIN.Impact,
  TKT_TT_MAIN.Priority,
  TKT_TT_MAIN.Manager_Group,
  TKT_TT_MAIN.Assignee_Group,
  TKT_TT_MAIN.CTI_Category,
  TKT_TT_MAIN.CTI_Type,
  STK_BOB_DATE(TKT_FLW_WORKLOG.Create_Date),
  TKT_TT_MAIN.CTI_Item,
  TKT_FLW_WORKLOG.Truncated_Work_Log
FROM
  TKT_TT_MAIN,
  TKT_FLW_WORKLOG
WHERE
  ( TKT_FLW_WORKLOG.TT_ID(+)=TKT_TT_MAIN.TT_ID  )
  AND  (
  TKT_TT_MAIN.TT_ID  =  @variable('Enter STK ID')
  )

[szlamany]

(+)=??

This must not be MS SQL - what is the backend DB??

[si_the_geek]

I could be wrong, but I think that is Oracle syntax for an Outer Join.

[szlamany]

I'm not sure what fields the OP would use - and that SELECT that's shown didn't make it any clearer to me...

My query should work in ORACLE as well - it's just not using the right WHERE clause in the sub-query (I'm guessing).