Extract data from largest date
I am having recods as below
-开发者_如何学Python--------------------------------------------------------------------
| AcnttNo | Date1 | Balance1 | Date2 | balance3 | date4 | balance4 |
|--------------------------------------------------------------------
| 123 | 50282 | 3456 | 45465 | 56557 | 4556 | 324235 |
| 123 | 56757 | 23434 | 234235 | 344324 | 56476 | 5676 |
| 123 | 435 | 2434 | 2343 | 234545 | 24245 | 2423424 |
---------------------------------------------------------------------
For example:
for each AcnttNo
there will be several rows of data for balance and date.
I'm using PL/SQL developer and an oracle database
If you want the row with the greatest date:
select
*
from
YourTable y
where
greatest(y.date1, y.date2, y.date3) =
(select max(greatest(yx.date1, yx.date2, yx.date3))
from
YourTable yx)
If you do actually need the balance matching the greatest date on that row:
select
greatest(y.date1, y.date2, y.date3) as GreatestDate,
case greatest(y.date1, y.date2, y.date3)
when y.Date1 then
y.balance1
when y.date2 then
y.balance2
when y.date3 then
y.balance3
end as GreatestDateBalance
from
YourTable y
where
greatest(y.date1, y.date2, y.date3) =
(select max(greatest(yx.date1, yx.date2, yx.date3))
from
YourTable yx)
But I think what you really need, is to reconsider your table design. :)
I'm not sure why you've multiple dates / balances in your table, however, the below should get you something interesting that you can work on...
SELECT *
FROM YourTable T
WHERE NOT EXISTS (
SELECT *
FROM YourTable T2
WHERE T2.AcntNo = T.AcntNo
AND T2.Date1 > T.Date1
)
精彩评论