[sql] 來自近一個月營收創新高之公司

假設我有一個 OperatingIncome table，schema 大概長這樣


CREATE TABLE operatingincome
(
  stock_code text NOT NULL,
  activity_date date NOT NULL,
  income double precision
);

那要怎麼找出「近一個月營收創新高之公司」？



select * from
(
    select A.stock_code, max(B.activity_date) as date
    from
        (
            select stock_code, max(income) as income from OperatingIncome
            group by stock_code
        ) as A,
        OperatingIncome as B
    where
    B.income = A.income and B.stock_code = A.stock_code
    group by A.stock_code
) as C
where C.date = '2012-12-31'