sql - How can I create a query which yields the MAXDATE for each order? -

i have table called test 5 columns: clientname, clientid, productnum, ordernum, orderdeliverydate, , thousands of rows. stores orders. each order can composed of several product items, , each product item can have specific delivery date.

i need table shows, each ordernum latest orderdeliverydate rest of test table columns (nb: null values must excluded because should not considered valid dates).

i know need use join, can't find solution.

select *  `test` inner join     (select           ordernum, max(orderdeliverydate) maxdate      test      group ordernum) groupedorders on test.ordernum = groupedorders.ordernum                                       , test.orderdeliverydate = groupedorders.maxdate group ordernum order groupedorders.maxdate asc 

can me solve this?

thank help.

you dont need join, unless want hook in table. need take max value. null values excluded groupings, if needed, add where orderdeliverydate not null

select ordernum, max(orderdeliverydate) maxdeliverydate    test group ordernum 

if however, need show more data, not ordernum , latest delivery date, please detail.

edit: brings latest delivery date until yesterday

select  t.*     test t         inner join(                 select ordernum, max(orderdeliverydate) maxdeliverydate                 test                 orderdeliverydate <= (yesterday)                 group ordernum) mx             on t.ordernum= mx.ordernum             , t.orderdeliverydate = mx.maxdeliverydate 

edit2: following query brings records have latest delivery date = yesterday

select  t.*     test t         inner join(                 select ordernum, max(orderdeliverydate) maxdeliverydate                 test                 group ordernum) mx             on t.ordernum= mx.ordernum             , t.orderdeliverydate = mx.maxdeliverydate             , mx.maxdeliverydate = =date_sub(curdate(),interval 1 day)