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 FROM X 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
- Dozens more answers to tough SQL questions from Jason Law are available here.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.