Get a single max date if dates are not unique

For sql 2000, Very similar to what I asked here

Get distinct max date using SQL

But this time the dates aren't unique so for this table pc_bsprdt_tbl

pc_bsprhd_key         pc_bsprdt_shpiadt               pc_bsprdt_prod
21ST 99-00            2001-04-30 23:59:59.000         72608-12895 
21ST 99-00            2001-04-30 23:59:59.000         72608-12910
AFCC990915            1999开发者_JAVA技巧-09-01 00:00:00.000         72608-12115    
AFCC990915            1999-09-01 00:00:00.000         CHU99-01514
AFCC990915            1999-09-01 00:00:00.000         POP99-01514

I would like returned

21ST 99-00            2001-04-30 23:59:59.000
AFCC990915            1999-09-01 00:00:00.000

Now, the pc_bsprdt_prod is unique so what I have tried is using the max for the product like this to give me uniqueness.

Select T.pc_bsprhd_key, T.pc_bsprdt_shpiadt
From pc_bsprdt_tbl As T
    Join    (
        Select pc_bsprhd_key, Max( T1.pc_bsprdt_shpiadt ) As MaxDateTime, Max(pc_bsprdt_prod) as Product
        From pc_bsprdt_tbl As T1
        Group By T1.pc_bsprhd_key
        ) As Z
    On Z.pc_bsprhd_key = T.pc_bsprhd_key
        And Z.MaxDateTime = T.pc_bsprdt_shpiadt
    AND Z.Product = T.pc_bsprdt_prod

It seems like it works :)

Is there a way to do it though just using the date? Maybe a top 1 in there somewhere?

SELECT pc_bsprhd_key, MAX(pc_bsprdt_shpiadt)
  FROM pc_bsprdt_tbl
 GROUP BY pc_bsprhd_key;

That might not be working as you think it is. That will give you the MAX(Date) and MAX(prod) which might not be on the same row. Here is an example:

    CREATE TABLE #Test
(
    a int,
    b date,
    c int,
)

INSERT INTO #Test(a, b, c)
SELECT 1, '01/01/2010', 3 UNION ALL
SELECT 1, '01/02/2010', 2 UNION ALL
SELECT 1, '01/03/2010', 1 UNION ALL
SELECT 2, '01/01/2010', 1

SELECT a, MAX(b), MAX(c) FROM #TEST
GROUP BY a

Which will return

----------- ---------- -----------
1           2010-01-03 3
2           2010-01-01 1

Notice that 1/03/2010 and 3 are not in the same row. In this situation I don't think it matters to you, but just a heads up.

As for the actual question- in SQL2005 we would probably apply a ROW_NUMBER over the groups to get the row with the latest date for each part, however you don't have access to this feature in 2000. If the above is giving you correct results I'd say use it.