Problem solve Get help with specific problems with your technologies, process and projects.

Max within a subset

I tried the solution in the question "How to get a max value for a row" but my results are not quite right... I am trying to return the record that was added most recently based on a combination of the Month and year and grouped by ID1 and ID2. The month and year are in separate fields.

The result I am getting is giving me only rows with the max month and year for the entire table, not the max for the subset of records that share a single ID1 and ID2.


DBMS SQL server 2000
Table = X

ID1 ID2 frshMonth frshYear
01  ab      05      2002
01  ab      06      2002
02  cd      07      2002
03  ef      07      2002

Results: only returns record with ID1 = 02 or 03,
 but should also return the record with ID1=01 and frshMonth=6

Select ID1, ID2, frshMonth, frshYear
WHERE frshMonth=(Select max(frshMonth) from X where ID1=X.ID1 and ID2=X.ID2)
	and frshYear = (Select max(frshYear) from X where where ID1=X.ID1 and ID2=X.ID2) 

Of course with SQL, there is generally more than one way to solve a problem. Here's one I like for this problem. I've combined the Year and Month, encoding the date into a single meaningful value. This solves the problem that arises when you compare the year and month separately. For example, we wouldn't want the max of 08/2002 and 03/2003 to result in a 08/2003. Here's the SQL and the resulting rows.

select * 
  from X x1
  where (frshYear*100)+frshMonth = ( select max((frshYear*100)+frshMonth)
                                       from X x2
                                       where x2.ID1 = x1.ID1
                                         and x2.ID2 = x1.ID2 )

ID1	ID2	frshMonth	frshYear
1	ab	6		2002
2	cd	7		2002
3	ef	7		2002

For More Information

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.