0

Have an SQL query for DB2 that returns the timestamp of when an event completed. It completes at 2 different date/times, which is the result expected.

That result completes at those 2 different times for the same entity. I desire that the entity be counted one time for the purposes of my query.

How would I get the query to return the result for only the first instance of completion for that entity?

  • Can this have only two entries per entity? Per hour, per minute, per day, etc? If only two (2) entries within a time period, choose the first – RLF Apr 27 '15 at 19:36
1

You don't specify what "first" means (or provide any table structure or example data), but let's assume it's the earliest completion timestamp:

  SELECT entity, min(completion_ts)
    FROM your_table
GROUP BY entity

If you are just getting results for a single entity, you can simplify even more:

  SELECT min(completion_ts)
    FROM your_table
   WHERE entity = 'XXX'
  • I am using the Min ( ) Over (Partition By) function. My results show both instances of the occurrence based on the timestamp. I am not retrieving for a single entity in that there are 5 viable entities. I need to obtain the first time it occurred in that entity. – user64264 Apr 28 '15 at 12:20
  • I am using the Min ( ) Over (Partition By) function. My results show both instances of the occurrence based on the timestamp. I am not retrieving for a single entity in that there are 5 viable entities. I need to obtain the first time it occurred in that entity. The data is coming from different tables. There is a CTE first pulling table from one table that is then referenced in a second CTE and joined to another table. This is where the result of the 2 instances occurs and I need to have just the first instance. – user64264 Apr 28 '15 at 12:28
  • I am using the MIN () OVER (Partition By) here in the second but am not getting the differentiation of selecting first per entity. – user64264 Apr 28 '15 at 12:29
  • Unless you post more information: Query, DDL and example data, you won't get a definitive answer. – Ian Bjorhovde Apr 28 '15 at 16:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.