不用row_number() over() 如何獲得這樣的數據?

Question

現有數據如下：
    NAME STORAGE_LIFE QTY MAKE_DATE SPECW SPECL LOT_NO    BARCODE CDAY
    AA         24         1 2007/9/2 105 20 79127004     A0005 2007.11.13
    AA         24         1 2007/12/1 105 20 7$067008     A0005 2008.06.28
    AA         24         1 2007/9/2 110 20 79127004     A0005 2007.11.29
    AA         24         1 2007/9/2 110 20 79127004     A0005 2007.12.19
    AA         24         1 2008/2/1 110 20 82137006     A0005 2008.07.23
    AA         24         2 2008/1/1 130 20 81217004     A0005 2008.07.11
    AA         24         2 2008/6/1 130 20 86144701     A0005 2008.09.09
    AA         24         1 2007/9/2 170 20 79127004     A0005 2007.12.15
    AA         24         1 2007/10/1 170 20 7$112204     A0005 2008.01.09我想要得到相同的規格（specw，specl）分組后日期（cday）最大的一條記錄這樣的SQL不用row_number() over()函數怎樣寫？要寫報表，因為Report Builder版本低不支持這個分析函數

Answer 1

select specw，specl,max(cday) from 表
group by specw，specl

Answer 2

NO,NO,NO我所有的列都還要的，不單單是要那幾列數據

Answer 3

select * from 
       (select specw a1，specl a2,max(cday) a3 from test order by specw，specl group by specw，specl) tab,test b
       where tab.a1=b.specw and tab.a2=b.specl and tab.a3=b.cday
       ;

Answer 4

分组后最大日期若有相同的怎么办？
SELECT select specw，specl,cday from 表 
group by specw,specl having CDAY in(select max CDAY from 表)

Answer 5

SELECT max(NAME),max(STORAGE_LIFE),max(QTY),max(MAKE_DATE),SPECW,SPECL,max(LOT_NO),max(BARCODE),max(CDAY) from 表 
group by specw,specl having CDAY in(select max(CDAY) from 表)

Answer 6

SQL> select * from t;NAME                                            STORAGE_LIFE                                     QTY MAKE_DATE                                                                                      SPECW                                   SPECL LOT_NO                                   BARCODE                                  CDAY
-------------------- --------------------------------------- --------------------------------------- ------------------------------------------------------------ --------------------------------------- --------------------------------------- ---------------------------------------- ---------------------------------------- -----------
aa                                                        24                                       1 2007/9/2                                                                                         105                                      20 111111                                   A0005                                    11/13/07
bb                                                        24                                       1 2007/9/2                                                                                         105                                      20 111111                                   A0005                                    11/10/07
cc                                                        24                                       1 2007/9/2                                                                                          88                                      18 111111                                   A0005                                    9/10/07
dd                                                        20                                       1 2007/9/2                                                                                          88                                      18 111111                                   A0005                                    8/10/07SQL> 
SQL> select * from t a where  not exists (
  2       select 1 from t b where (b.SPECW=a.SPECW and b.SPECL=a.SPECL) and b.cday>a.cday
  3  );NAME                                            STORAGE_LIFE                                     QTY MAKE_DATE                                                                                      SPECW                                   SPECL LOT_NO                                   BARCODE                                  CDAY
-------------------- --------------------------------------- --------------------------------------- ------------------------------------------------------------ --------------------------------------- --------------------------------------- ---------------------------------------- ---------------------------------------- -----------
aa                                                        24                                       1 2007/9/2                                                                                         105                                      20 111111                                   A0005                                    11/13/07
cc                                                        24                                       1 2007/9/2                                                                                          88                                      18 111111                                   A0005                                    9/10/07