0

I am trying my hardest to get a list of the most recent rows by date in a DB2 file. The file has no unique id, so I am trying to get the entries by matching a set of columns. I need DESCGA most importantly as that changes often. When it does they keep another row for historical reasons.

SELECT B.COGA, B.COMSUBGA, B.ACCTGA, B.PRFXGA, B.DESCGA
FROM   mylib.myfile B
WHERE
(
    SELECT COUNT(*)
    FROM
    (
        SELECT A.COGA,A.COMSUBGA,A.ACCTGA,A.PRFXGA,MAX(A.DATEGA) AS EDATE
        FROM mylib.myfile A
        GROUP BY A.COGA, A.COMSUBGA, A.ACCTGA, A.PRFXGA
    ) T
    WHERE
        (B.ACCTGA = T.ACCTGA AND
        B.COGA = T.COGA AND
        B.COMSUBGA = T.COMSUBGA AND
        B.PRFXGA = T.PRFXGA AND
        B.DATEGA = T.EDATE)
) > 1

This is what I am trying and so far I get 0 results.

If I remove

B.ACCTGA = T.ACCTGA AND

It will return results (of course wrong).

I am using ODBC in VS 2013 to structure this query.

I have a table with the following

| a | b | descri | date     |
-----------------------------
| 1 | 0 | string | 20140102 |
| 2 | 1 | string | 20140103 |
| 1 | 1 | string | 20140101 |
| 1 | 1 | string | 20150101 |
| 1 | 0 | string | 20150102 |
| 2 | 1 | string | 20150103 |
| 1 | 1 | string | 20150103 |

and i need 

| 1 | 0 | string | 20150102 |
| 2 | 1 | string | 20150103 |
| 1 | 1 | string | 20150103 |
Vinez
  • 560
  • 2
  • 11
  • 2
    Sample data and desired results would do more than a non-working query in conveying what you are trying to do. – Gordon Linoff Jan 06 '16 at 21:47
  • What @GordonLinoff said, and the table definition too. And what version of System i are you accessing. – mustaccio Jan 06 '16 at 22:36
  • If you have **no** unique key you're mostly screwed in SQL land (native I/O allowing strange tricks, barring reorgs) You **probably** have a unique _composite_ key, which is made up of several columns. – Clockwork-Muse Jan 07 '16 at 07:08
  • @Clockwork-Muse I was actually going to try and use the RRN but I wasnt sure how to get a hold of it same with the composite key – Vinez Jan 07 '16 at 13:40
  • Your statement doesn't match your sample data - your query lists 5 columns but the data has 4. It also doesn't match the column names, so is somewhat less helpful. At the moment, it looks like `SELECT ... MAX(date) ... GROUP BY a, b, descri` would work... `RRN()` is available as a function (essentially), but **don't** rely on it. It's based on the underlying table, which usually _doesn't_ match your desired results: remember data in SQL tables is essentially UNORDERED. It also gets mucked with during reorgs and other things. – Clockwork-Muse Jan 07 '16 at 14:06
  • Possible duplicate of [Select first row in each GROUP BY group?](http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) – Clockwork-Muse Jan 08 '16 at 02:56

1 Answers1

0

You can use row_number():

select t.*
from (select t.*,
             row_number() over (partition by a, b order by date desc) as seqnum
      from mylib.myfile t
     ) t
where seqnum = 1;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • @GordonLinoff - if you're going to give an answer like that, we really ought to close this as a duplicate of one of the existing [tag:greatest-n-per-group] questions.... – Clockwork-Muse Jan 08 '16 at 02:57