How to select from the database the areas whose area has increased?

Question

SQL

Goodnight. There is a table
Number - line
Area - fractional number
Date - date.
What query can be used to select parcels that existed before the last date and whose area was less than the last one?

Answer 1

SELECT `t2`.`number`, `t2`.`area` AS `newarea`, `t2`.`date` AS `lastdate`,
        `t3`.`area` AS `oldarea`, `t3`.`date` AS `prevdate`
  FROM (
    SELECT `number`, MAX(`date`) AS `maxdate`
      FROM `table`
      GROUP BY `number`
  ) AS `t1`
  JOIN `table` AS `t2` ON `t2`.`number` = `t1`.`number` AND `t2`.`date` = `t2`.`maxdate`
  JOIN `table` AS `t3` ON `t3`.`number` = `t1`.`number` AND `t3`.`date` < `t3`.`maxdate`
      AND `t3`.`area` < `t3`.`area`

Answer 2

IF OBJECT_ID('tempdb..#testtable') IS NOT NULL	DROP TABLE #testtable

create table #testtable (number nvarchar(255), spac money, dateR date)
insert into #testtable
(number, spac, dateR)
values
(1,10,'20140505'),
(1,11,'20141212'),
(2,50,'20160505'),
(2,40,'20160508')

IF OBJECT_ID('tempdb..#testtableRNK') IS NOT NULL	DROP TABLE #testtableRNK

select * ,
rank() over (partition by Number order by dateR desc) as RNK
into #testtableRNK
from #testtable

select * from #testtableRNK  r1
inner join #testtableRNK  r2 ON r1.number= r2.number and r2.RNK=(r1.RNK+1)  and r1.RNK=1 and r2.spac< r1.spac