CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Patrick
Patrick

Reputation: 2577

How can I select the max value of a column based on other column values?

I'm looking at a table that is set up sort of like this: 

Month  |  Year  |  Item  
1      |  2016  |  a
1      |  2016  |  b
1      |  2016  |  c
2      |  2016  |  a
2      |  2016  |  b
2      |  2016  |  c
3      |  2016  |  a
3      |  2016  |  b

The I want to get the highest month and year values when a, b, and c are all present, in this case 2, 2016.

Upvotes: 0

Views: 38

Answers (1)

Skippy
Skippy

Reputation: 1590

Allowing for other values appearing in Item column, you could use:

SELECT TOP (1) Year, Month
FROM myTable
WHERE Item in ('a', 'b', 'c')
GROUP BY Year, Month
HAVING COUNT(DISTINCT Item) = 3
ORDER BY Year DESC, Month DESC

If you are only ever going to have Item values of 'a', 'b' or 'c' then you can omit the where clause.

Upvotes: 2

Related Questions