CODEWITHSUNDEEP
sqldb2
Crypto营长
Crypto营长

Reputation: 179

How to retrieve the last record in each group with DB2?

As for MySQL, I know how to retrive the last record in each group, such as:

SELECT A.* 
FROM (
  SELECT * 
  FROM devops.clearquest_clearquest 
  ORDER BY 'created_at' DESC
) AS A 
GROUP BY A.submitter;

But DB2 is a little bit different, you can only select column which is used after GROUP BY, like this:

SELECT A.submitter 
FROM (
  SELECT * 
  FROM devops.clearquest_clearquest 
  ORDER BY 'created_at' DESC
) AS A 
GROUP BY A.submitter;

So, if I want to fetch two columns, I need to GROUP BY them like this:

SELECT A.column01, A.column02 
FROM (
  SELECT * 
  FROM devops.clearquest_clearquest 
  ORDER BY 'created_at' DESC
) AS A 
GROUP BY A.column01, A.column02;

here comes the problem, I can't retrieve the last record in each group if I GROUP BY two columns. I'm new to DB2, please help.

Upvotes: 0

Views: 4706

Answers (4)

Radim Bača
Radim Bača

Reputation: 10701

It is a classical greatest per group task and I would use some standard solution such as

SELECT cc.*
FROM devops.clearquest_clearquest cc 
JOIN (
    SELECT A.submitter, max('created_at') max_created_at
    FROM devops.clearquest_clearquest A
    GROUP BY A.submitter
) t ON cc.submitter = t.submitter 
   and cc.'created_at' = t.max_created_at

or window functions

SELECT * 
FROM (
    SELECT *,
      row_number() over (partition by submitter order by 'created_at' desc) rn
    FROM devops.clearquest_clearquest 
) t
WHERE t.rn = 1

Please note that the behaviour of MySQL is not 'standard'. Most of the database systems do not allow to include columns behind SELECT if they are not part of the GROUP BY or aggregate function. If you set sql_mode of MySQL to ANSI then your query will not pass.

Upvotes: 1

VAI Jason
VAI Jason

Reputation: 544

You can query the table and join it to itself to get the max date (or whatever you want to group by/find

For example, 

select t1.submitter, XXGroup2, t2.maxdate from
    (select * FROM devops.clearquest_clearquest) t1
        inner join
    (SELECT submitter, XXGroup2, max(created_at) as maxdate 
     FROM devops.clearquest_clearquest 
     GROUP BY XXGroup1, XXGroup2) t2                                                    
        on t1.submitter = t2.submitter AND t1.XXGROUP2 = t2.XXGROUP2
)                                                                       
order by t1.submitter, t1.XXGROUP2

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

This is not proper SQL:

SELECT cc.* 
FROM (SELECT * 
      FROM devops.clearquest_clearquest 
      ORDER BY created_at DESC
     ) cc
GROUP BY cc.submitter;

Note: No quotes around created_at.

Because you have columns in the SELECT that are not GROUP BY keys and not the arguments to aggregation functions. This will return a syntax error in almost all databases. Happily, it will also break in the more recent versions of MySQL (with the default options).

Often the most efficient equivalent is:

select cc.*
from devops.clearquest_clearquest cc
where cc.created_at = (select max(cc2.created_at)
                       from devops.clearquest_clearquest cc2
                       where cc2.submitter = cc.submitter
                      );

With an index on devops.clearquest_clearquest(submitter, created_at), this is often the fastest approach in any database.

Other answers have provided the answer using row_number(). That is also a very good solution, but often a wee bit slower, because the row number is assigned to all rows before most are filtered out.

Upvotes: 1

Paul Vernon
Paul Vernon

Reputation: 3901

In Db2 you can do this to get the row with the highest created_at date within each group column01, column02.

Wwell, strictly speaking it would be one of the rows that has the highest created_at in each group. ROW_NUMBER() is non-deterministic if created_at, column01, column02 is not unique

SELECT * 
FROM (
    SELECT *
    ,    row_number() over (
                  partition by A.column01, A.column02
                  order by "created_at" desc) rn
    FROM devops.clearquest_clearquest A
) t
WHERE t.rn = 1

Note that column names need to be surrounded by double quotes (and not single quotes) if they are not upper-case in the table defintion

Upvotes: 1

Related Questions