SQLTeam.com | Weblogs | Forums

Find the max value in column A and display it's corresponding value in column B

Hi! I have a table that looks something like this:

YEAR DATE
2016 11/2/2016
2016 11/2/2016
2015 11/8/2015
2015 11/8/2015

I need to write a statement that basically says "find the most recent year and display it's corresponding date." In the example above, the output should be 11/2/2016. I have tirelessly tried several function variations but I can't seem to make it work. Can anyone help?

Kate
(Newbie)

select a.Date from ( select [date], rank() over (order by [year] desc) myRank from myTable ) a where a.myRank = 1

Thanks for the quick response! I'm not sure how or where to insert that into the code. Is there a way to input that as an expression in the cell?

SQL Server database tables do not have cells. It sounds like you might be working with Excel or something similar. If so, you might try an Excel related forum.

I am working in SQL BIDS 2008 R2. I'm used to Excel though, which is why I used the term cell. I am looking to insert it as an expression using this tool:

MAX(Date) should give you the most recent date, which will also be the most recent year.

I tried that first but soon realized that it kept returning the max value based on the day (i.e., it returned 11/8/2015, not 11/2/2016, because "8" is bigger than "2"). I checked the format of the field and confirmed that it is formatted correctly as a date.

Ouch. If it's text, you'll need to CAST it as a date.

MAX(CAST(Date AS date))

If you get invalid value errors, you'd have to do this:

WHERE ISDATE(Date) = 1

Or this:
MAX(CAST(CASE WHEN ISDATE(Date) = 1 THEN Date ELSE 0 END AS date))

1 Like

Thanks, that worked! Well sort of. It wasn't recognizing CAST for some reason but it did recognize CDate. Thanks so much for pointing me in the right direction! :relaxed:

Kate