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

1

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)

2

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

3

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?

4

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.

5

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:

6

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

7

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.

8

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
9

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