Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

How to return a single row with a maximum column value from a sub query

Avatar of skaleem1
skaleem1Flag for Canada asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008
10 Comments1 Solution422 ViewsLast Modified:
The sub-query in the following statement returns two records as given below:


                  SELECT TMP.TOTAL_ACRES, TMP.YEAR, SD1.*
                  FROM      
                  (SELECT      CA.ACCOUNT_ID, CA.YEAR - 2 AS YEAR, SUM(CA.ACRES) AS TOTAL_ACRES
                         FROM      dbo.Table1 CA INNER JOIN
                              dbo.Table2 A ON
                                    A.ACCOUNT_ID = CA.ACCOUNT_ID
                         WHERE CA.INACTIVE_FLAG IS NULL
                         GROUP BY CA.ACCOUNT_ID, CA.YEAR - 2) tmp
                  INNER JOIN
                  dbo.Table3 SD1 ON
                  tmp.ACCOUNT_ID = SD1.ACCOUNT_ID AND
                  tmp.YEAR = SD1.PROGRAM_YEAR
                  WHERE      SD1.TOTAL_ACRES IS NULL AND SD1.ACREAGE_ESTIMATED IS NULL

Again, the sub-query in the above statement returns two records as given below:

ACCOUNT_ID      YEAR      TOTAL_ACRES
10205467                        2007       1825
10205467                        2008       1825

How can I change the sub-query to only get a single record with the largest number of year, in this case year 2008, i.e. as follows:

ACCOUNT_ID      YEAR      TOTAL_ACRES
10205467                        2008       1825

Please help by changing the above query!
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Commented:
This problem has been solved!
Unlock 1 Answer and 10 Comments.
See Answers