Get distinct max date using SQL

Question

I'm not sure quite how to title my question but this is what I'm trying to do:

Given pc_tmppl_tbl

pc_tmppl_attach pc_tmppl_val1    pc_tmppl_crtdt

AJC05-06    AJCINT       2005-08-15 10:32:03.790
AJC06-07    AJCINT       2006-10-17 10:02:06.570
AJC07-08    AJCINT       2007-06-13 10:44:53.573
AJC08-09    AJCINT       2008-06-27 09:51:17.290
AJC09-10    AJCINT       2009-07-20 14:26:06.270
AJC10-11    AJCINT       2010-08-26 11:54:32.777
AJC99-001   AJCINT       2005-05-30 19:30:51.623
ALPI05-06   ALPINE       2005-05-30 19:30:51.623
ALPI07-08   ALPINE       2006-12-11 13:57:09.923
ALPI07-08   ALPINE       2007-05-24 14:04:07.867
ALPI08-09   ALPINE       2008-04-30 09:49:24.140

I want it to return the max date for pc_tmppl_crtdt and its corresponding pc_tmppl_attach so

ALPI08-09   ALPINE       2008-04-30 09:49:24.140
AJC10-11    AJCINT       2010-08-26 11:54:32.777

I've been trying sub queries but haven't quite hit on the answer, any help would be much appreciated.

Are the dates absolutely guaranteed to be unique within a given pc_tmppl_val1 value? — Pete M, Apr 13 '11 at 20:59
The data is a little strange, it looks like around 2005 some automated process? created some of them so those don't have unique date. If I filter after say 2009 then they have unique dates. — mellerbeck, Apr 13 '11 at 21:15
I realize you may not know this because you are new. It helps us help you if you include the specific product and version in the tags. I've updated your tag to specify SQL Server 2000 since you mentioned that in the comments to marc_s. — Thomas, Apr 13 '11 at 21:16
Thanks Thomas! Yeah gotta get all the details in there for you guys! — mellerbeck, Apr 13 '11 at 21:18

score 3 · Accepted Answer · answered Apr 13 '11 at 21:20

3

Select T.pc_tmppl_attach, T.pc_tmppl_val1, T.pc_tmppl_crtdt
From pc_temppl_tbl As T
    Join    (
            Select pc_tmppl_val1, Max( T1.pc_tmppl_crtdt ) As MaxDateTime
            From pc_temppl_tbl As T1
            Group By T1.pc_tmppl_val1
            ) As Z
        On Z.pc_tmppl_val1 = T.pc_tmppl_val1
            And Z.MaxDateTime = T.pc_tmppl_crtdt

answered Apr 13 '11 at 21:20

Thomas

63,911
12
95
141

Thomas, I don't know protocol for follow up questions but now I am trying to join the above to the pc_bsprdt_tbl – mellerbeck Apr 14 '11 at 04:14
Thomas, I don't know protocol for follow up questions but now I am trying to join the above to the pc_bsprdt_tbl it would join on the pc_bsprdt_tbl.pc_bsprhd_key = pc_tmppl_tbl.pc_temppl_val1 but can't quite get it :) – mellerbeck Apr 14 '11 at 04:21
@mellerbeck - Normally, the protocol would be to create a new question. However, given what you have said, you should be able to simply add a join clause along the lines of: `Join pc_bsprdt_tbl On pc_bsprdt_tbl.pc_bsprhd_key = T.pc_temppl_val1`. – Thomas Apr 14 '11 at 04:30
gah, It works, I just need to use the right field! Thanks again! – mellerbeck Apr 14 '11 at 04:35

score 2 · Answer 2 · answered Apr 13 '11 at 20:58

You didn't define what server and version you're using - if you're on SQL Server 2005 or newer, you can use a CTE (Common Table Expression) and a ranking function - something like this:

;WITH PartitionedData AS
(
   SELECT 
       pc_tmppl_attach, pc_tmppl_val1, pc_tmppl_crtdt,
       ROW_NUMBER() OVER(PARTITION BY pc_tmppl_val1 
                         ORDER BY pc_tmppl_crtdt DESC) AS 'RowNumber'
  FROM dbo.pc_tmppl_tbl
)
SELECT
   pc_tmppl_attach, pc_tmppl_val1, pc_tmppl_crtdt
FROM
   PartitionedData
WHERE
   RowNumber = 1

Basically, what the CTE (inner select) does is grab all data from your table, partition it by your column pc_tmppl_val1 - so each group of values for pc_tmppl_val1 starts counting back at 1 - and order those entries by pc_tmppl_crtdt descending - newest entry is first.

So for each pc_tmppl_val1 value, the newest entry is the entry with the RowNumber = 1 and that's what the outer SELECT (based on the CTE) gives you.

The CTE and ranking function are very flexible, too - if you need to top 3 entries for each pc_tmppl_val1 value, just change the outer WHERE condition to

WHERE RowNumber <= 3

and you're done!

The CTE (Common Table Expression) and ranking functions are ANSI SQL standard - so other databases besides Microsoft SQL Server support it, too (I just know SQL Server the best - that's why I use it as a sample).

MPelletier · Answer 3 · 2011-04-14T13:50:29.490

1

Using a correlated query (which should work with most sql databases):

SELECT pc_tmppl_attach,pc_tmppl_val1, pc_tmppl_crtdt
FROM pc_tmppl_tbl AS tbl_ext
WHERE pc_tmppl_crtdt = (
                        SELECT MAX(tbl_int.pc_tmppl_crtdt)
                        FROM pc_tmppl_tbl AS tbl_int
                        WHERE tbl_int.pc_tmppl_val1 = tbl_ext.pc_tmppl_val1
                       )

edited Apr 14 '11 at 13:50

answered Apr 13 '11 at 21:01

MPelletier

16,256
15
86
137

This really looks the closest but I get a "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference." – mellerbeck Apr 13 '11 at 21:16
@mellerbeck, @ChrisBuckler: I made a correction, it works in SQLServer 2008, untested in 2000. – MPelletier Apr 14 '11 at 13:58

score -1 · Answer 4 · answered Apr 13 '11 at 21:04

-1

SELECT TOP 1 [pc_tmppl_attach], [pc_tmppl_crtdt]
FROM [Given pc_tmppl_tbl]
order by pc_tmppl_crtdt desc

answered Apr 13 '11 at 21:04

Starwfanatic

584
2
13
29

2

Downvote isn't from me, but your answer will only yield one result. The question calls for all maximums. – MPelletier Apr 13 '11 at 21:10
I think its more that, the question was not clear when first posted... as there were 5 or 6 of use that implemented a solution simular to yours. – clamchoda Apr 14 '11 at 13:34

Get distinct max date using SQL

4 Answers4

Linked