Get a single max date if dates are not unique

Question

For sql 2000, Very similar to what I asked here

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-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?

Your example output wants just 2 columns; your example query generates 3 columns. Which is actually what you want? — Jonathan Leffler, Apr 18 '11 at 16:14
Ahh True, thanks Jonathan. Fixed, I only care about the 2 columns — mellerbeck, Apr 18 '11 at 16:22

score 3 · Answer 1 · answered Apr 18 '11 at 17:12

3

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

answered Apr 18 '11 at 17:12

Jonathan Leffler

730,956
141
904
1,278

score 1 · Answer 2 · answered Apr 18 '11 at 16:24

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.

I don't think it should matter since it will return me one row for 21ST 99-00 etc... (but my brain was being tickled about whether it was giving me correct results) hence the question :) — mellerbeck, Apr 18 '11 at 16:30

Get a single max date if dates are not unique

2 Answers2