Get Max() record from table by group

Question

Hi, i've got a table like that :

Article Number  Last Voucher Number Last Voucher Date
0557934         519048                  04/02/2005
0557934         519067                  04/02/2005
0557934         528630                  09/29/2005
0557934         528631                  09/29/2005
0557934         529374                  10/13/2005
0557934         529375                  10/13/2005
0557934         529471                  10/16/2005
0557934         529472                  10/16/2005
0557934         535306                  01/08/2006
0557934         535307                  01/08/2006
0557934         1106009                 08/10/2006
0557934         1106010                 08/10/2006
0022738         22554                   02/20/1995
0022738         22595                   03/12/1995
0022738         22597                   03/15/1995
0022738         22605                   03/19/1995
0022738         22616                   03/25/1995
0022738         22621                   03/28/1995
0022738         22630                   04/05/1995

I would like to have only the record with the last date :

Article Number  Last Voucher Number Last Voucher Date
0557934         1106010                 08/10/2006
0022738         22630                   04/05/1995

I can do directly on SQL or on Linq.

Any idea?

Ju

Answer 1

The simplest way is probably using row_number. This also breaks ties for the same voucher date:

select *
from (
    select 
        *
    ,   row_number() over (partition by [Article Number] 
          order by [Last Voucher Date] desc, [Last Voucher Number] desc) as RowNr
    from YourTable
) as SubQueryAlias
where RowNr = 1

Another option is a where not exists clause:

select *
from YourTable yt
where not exists (
    select *
    from YourTable yt2
    where yt.[Article Number] = yt2.[Article Number]
    and yt.[Last Voucher Date] < yt2.[Last Voucher Date]
)

In case of ties for the latest voucher date, this would return both rows.

Answer 2

You need to extract the the last date per article, then join back to pick up Last Voucher Number. Otherwise, you'll group on Last Voucher Number too.

Of course, this returns 2 rows for article 0557934 but what is the tie break criteria for the same date? This performs better more often that using ROW_NUMBER I've found, but YMMV.And ROW_NUMBER is how you could deal with the tiebreak situation

SELECT
    M.*
FROM
    (
    SELECT
        MAX([Last Voucher Date]) AS LastDate,
        [Article Number]
    FROM
        Mytable
    GROUP BY
        [Article Number]
    ) MMax
    JOIN MyTable M ON MMax.[Article Number] = M.[Article Number] AND MMax.LastDate = M.[Last Voucher Date]

Answer 3

If you're using Oracle, you would do something like this:

select *
from 
  (select *
  from Table
  order by voucher_date)
where rownum = 1