SQL - 根据2列和条件返回唯一行

I have a table on HSQLDB with data as

我有一个HSQLDB表，数据为

Id   Account    Opendate    Baldate      LastName ........ State
1    1234       040111      041217       Jackson           AZ 
2    1234       040111      051217       James             FL 
3    2345       050112      061213       Thomas            CA
4    2345       050112      061213       Kay               DE

How can i write a query that gives me rows that have distinct values in Account and Opendate columns, having the maximum Baldate. If Baldate is also same, then return the first row ordered by Id.

我如何编写一个查询，该查询为我提供了在Account和Opendate列中具有不同值的行，具有最大Baldate。如果Baldate也相同，则返回Id排序的第一行。

So the resultset should contain

所以结果集应该包含

Id   Account    Opendate    Baldate      LastName........State
2    1234       040111      051217       James           FL
3    2345       050112      061213       Thomas          CA

I have gotten this far.

我已经走到了这一步。

select LastName,...,State, max(BalDate) from ACCOUNTS group by Account, Opendate

But the query fails since I cannot use an aggregate function for columns not in group by (lastname, state etc). How can I resolve this?

但是查询失败了，因为我不能将聚合函数用于不在group by（lastname，state等）中的列。我该如何解决这个问题？

2 个解决方案

#1

HSQLDB supports correlated subqueries, so I think this will work:

HSQLDB支持相关子查询，所以我认为这将有效：

select a.*
from accounts a
where a.id = (select a2.id
              from accounts a2
              where a2.account = a.account and a2.opendate = a.opendate
              order by baldate desc, id asc
              limit 1
             );

#2

I'm not familiar with hslqdb, so this is just ANSI. I see that it doesn't support analytical functions, which would make life easier.

我不熟悉hslqdb，所以这只是ANSI。我发现它不支持分析功能，这会使生活更轻松。

If it works the other answer is a lot cleaner.

如果它工作，另一个答案是更清洁。

SELECT ACC_T1.Id,
       ACC_T1.Opendate,
       ACC_T1.Baldate
       ACC_T1.LastName,
       ...
       ACC_T1.State
  FROM Account_Table AS ACC_T1
 INNER
  JOIN (SELECT account,
               OpenDate,
               MAX(BalDate) AS m_BalDate
          FROM AccountTable
         GROUP
            BY account,
               OpenDate
       ) AS SB1
    ON ACC_T1.Account = SB1.Account
   AND ACC_T1.OpenDate = SB1.OpenDate
   AND ACC_T1.BalDate = SB1.m_BalDate
 INNER
  JOIN (SELECT account,
               OpenDate,
               BalDate,
               MIN(id) AS m_id
          FROM Account_Table
         GROUP
            BY account,
               OpenDate,
               BalDate
       ) AS SB2
    ON ACC_T1.Account = SB2.Account
   AND ACC_T1.OpenDate = SB2.OpenDate
   AND ACC_T1.BalDate = SB2.BalDate
   AND ACC_T1.id = SB2.m_id

#1