SELECT MAX DATE FROM MULTIPLE DATES

Question

this is my case:

I have 2 tables SKU & Disco_Dates.

In both tables there is a similar field.

SKU.[MFG_PART_NUMBER] = Disco_Dates.[MATERIAL]

I need to know the max DATE_UPDATE available for each MFG_PART_NUMBER

DISCO_DATE TABLE

+-------------+-----------------------+-----------------------+
| MATERIAL    |DISCO_DATE             | DATE_UPDATE           | 
+-------------+-----------------------+-----------------------+
|T6C25AW#ABC  |NULL                   |2016-09-14 11:15:03.587|
|T6C25AW#ABC  |2016-10-28 00:00:00.000|2016-09-21 13:45:03.591| 
|T6C25AW#ABC  |2016-10-31 00:00:00.000|2016-09-30 12:38:08.990| 
+-------------+-----------------------+-----------------------+

SKU TABLE

+---------------+
|MFG_PART_NUMBER| 
+---------------+
|T6C25AW#ABC    |
|G2F32UC#ABC    |
+---------------+

SQL QUERY

SELECT
 t1.[MFG_PART_NUMBER]
,t2.[DISCO_DATE]
,MAX (t2.[DATE_UPDATE]) as DATE_UPDATE

FROM Test.dbo.SKU t1

LEFT JOIN Test.dbo.Tbl_Disco_Dates t2
ON [MFG_PART_NUMBER] = [MATERIAL]

WHERE t1.[MFG_PART_NUMBER] = 'T6C25AW#ABC'

group by 
  t1.[MFG_PART_NUMBER]
 ,t2.[DISCO_DATE]

My current result is giving me all 3 rows.

This is what I need.

+----------------+-----------------------+-----------------------+
|MFG_PART_NUMBER |DISCO_DATE             | DATE_UPDATE           |  
+----------------+-----------------------+-----------------------+
|T6C25AW#ABC     |2016-10-31 00:00:00.000|2016-09-30 12:38:08.990| 
+----------------+-----------------------+-----------------------+

Thanks in advance.

Luis Lara · Accepted Answer · 2016-10-13 05:47:33Z

2

Try this:

SELECT
 t1.[MFG_PART_NUMBER]
,t2.[DISCO_DATE]
,t2.[DATE_UPDATE] as DATE_UPDATE

FROM Test.dbo.SKU t1

LEFT JOIN Test.dbo.Tbl_Disco_Dates t2
ON [MFG_PART_NUMBER] = [MATERIAL]

WHERE t1.[MFG_PART_NUMBER] = 'T6C25AW#ABC'
      AND t2.[DATE_UPDATE] = (SELECT MAX(t2.[DATE_UPDATE] FROM Test.dbo.Tbl_Disco_Dates t2)

edited Oct 13, 2016 at 5:47

Luis Lara

752 silver badges10 bronze badges

answered Oct 13, 2016 at 2:47

Wildan Maulana Syahidillah

9541 gold badge9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Luis Lara Over a year ago

Pretty close to the desired result. But it led me to find the answer SELECT t1.[MFG_PART_NUMBER] ,t2.[DISCO_DATE] ,t2.[DATE_UPDATE] as DATE_UPDATE FROM Test.dbo.SKU t1 LEFT JOIN Test.dbo.Tbl_Disco_Dates t2 ON [MFG_PART_NUMBER] = [MATERIAL] WHERE t1.[MFG_PART_NUMBER] = 'T6C25AW#ABC' AND t2.[DATE_UPDATE] = (SELECT MAX(t2.[DATE_UPDATE]) FROM Test.dbo.Tbl_Disco_Dates t2) group by t1.[MFG_PART_NUMBER] ,t2.[DISCO_DATE] ,t2.[DATE_UPDATE]

Wildan Maulana Syahidillah Over a year ago

Oh yes, I forgot MAX has to be in a SELECT statement

Kris Over a year ago

Does it still need the group by if you do it this way?

Luis Lara Over a year ago

@Kris Good tip, Group by is not needed.

this.hart · Accepted Answer · 2016-10-13 02:51:11Z

2

 declare @maxdate datetime
 select @maxdate = max(t2.[DATE_UPDATE]) FROM Test.dbo.SKU
 select * from ( SELECT
  t1.[MFG_PART_NUMBER]
 ,t2.[DISCO_DATE]
  , DATE_UPDATE

  FROM Test.dbo.SKU  where DATE_UPDATE=   @maxdate)   t1 
  LEFT JOIN Test.dbo.Tbl_Disco_Dates t2
   ON t1.[MFG_PART_NUMBER] = t2.[MATERIAL]

  WHERE t1.[MFG_PART_NUMBER] = 'T6C25AW#ABC'

  group by 
 t1.[MFG_PART_NUMBER]
 ,t2.[DISCO_DATE]

answered Oct 13, 2016 at 2:51

this.hart

3282 silver badges10 bronze badges

Comments

Tim Biegeleisen · Accepted Answer · 2016-10-13 02:50:42Z

1

Wouldn't a simple GROUP BY query work:

SELECT MATERIAL,
       MAX(DISCO_DATE) AS DISCO_DATE,
       MAX(DATE_UPDATE) AS DATE_UPDATE
FROM Test.dbo.Tbl_Disco_Dates
GROUP BY MATERIAL

answered Oct 13, 2016 at 2:50

Tim Biegeleisen

526k32 gold badges323 silver badges400 bronze badges

Collectives™ on Stack Overflow

SELECT MAX DATE FROM MULTIPLE DATES

3 Answers 3

4 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Related