Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2017
    Posts
    1

    Unanswered: Informix OVER (PARTITION BY) like function

    Hi guys,

    I am new to the forum and new to informix. I have worked mostly with MSSQL in the past.

    Now I have an informix DB with a table that has dupliate entries with a different timestamp.

    I need to get the latest records for each key...

    i.e.

    accountnumber, datetime, column1, column2, column3
    00001, 2017-01-01 01:01:01, 1, 1, 1
    00002, 2017-01-01 01:15:01, 1, 1, 1
    00003, 2017-01-01 01:37:01, 1, 1, 1
    00001, 2017-01-01 01:48:02, 2, 2, 2
    00002, 2017-01-01 02:07:02, 2, 2, 2
    00003, 2017-01-01 02:13:12, 2, 2, 2
    00001, 2017-01-01 02:50:10, 3, 3, 3
    00002, 2017-01-01 03:25:42, 3, 3, 3
    00003, 2017-01-01 04:15:32, 3, 3, 3

    I I need a query which would produce the following results

    accountnumber, datetime, column1, column2, column3
    00001, 2017-01-01 02:50:10, 3, 3, 3
    00002, 2017-01-01 03:25:42, 3, 3, 3
    00003, 2017-01-01 04:15:32, 3, 3, 3

    I made this work using something like "datetime>( (SELECT max(datetime) - X UNITS MINUTE)" because the time diff is usually a few minutes but this is not very reliable and will run into an issue where the query will wither return duplicate data or will not return all the data.

    In MSSQL this could be done with OVER (PARTITION BY). I have no idea how to do this in informix.

    Any help would be appreciated.

    Kenan

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    392
    Provided Answers: 1
    Hi Kenan,


    sorry for the delay. OVER / PARTITION BY has been implemented in the release 12.10 (xC1I think, not sure) as part of the OLAP functions implementation. Although Informix had less SQL statements than other DBMS at some point in time, it is a general trend that Informix' SQL syntax is getting enriched at almost every release, not to speak about NoSQL/JSON-BSON which is native in Informix since 2013: you can even make joins between regular SQL Data with NoSQL data in the same query.

    Back to your question, I recommend you start with Fred Ho's blog about OLAP functions which is very well done: he implemented this functionality in Informix, you can't have better source! ( you may have to register IBM developer works, but it is free of charge).

    you can also read the IBM documentation here
    Last edited by begooden-it; Today at 11:02.

Tags for this Thread

Posting Permissions

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