Easy select MAX() query

[pdbowling]

Hello, Everyone.
This should be pretty easy.

I am trying to select only the records with the maximum date from a table. Evidently, I do not clearly understand how the MAX() function actually works.

I am using this unsuccessful query:

SELECT My_ID,Ssn,Max(My_Date)
  FROM My_Table
 GROUP BY Ssn,My_ID
 ORDER BY Ssn

The data set would look like
1,111-11-1111,01/01/2008
2,111-11-1111,01/01/2010
3,111-11-1234,01/01/2009
4,111-11-1234,01/01/2007

I only want to see
2,111-11-1111,01/01/2010
3,111-11-1234,01/01/2009
because they are the Ssn with the largest date.

Thanks, Everyone
Patrick

 

[gmmastros]

Evidently, I do not clearly understand how the MAX() function actually works.

Let me try to explain it.

I suspect it's the GROUP BY part that you may not clearly understand. Basically, the group by clause in the query controls the output of the query.

In your query, you are grouping by My_ID and SSN. If you wrote a query like this:

Select Distinct My_ID, SSN From My_Table

You would get an output that has My_ID and SSN without any duplicates between those 2 columns. Group By works similarly to the DISTINCT query. Whatever aggregates you include in the query will be applied to this distinct list of rows.

If you want to get the latest date for each SSN, then you could write a query like this:

SELECT Ssn,Max(My_Date)
  FROM My_Table
 GROUP BY Ssn
 ORDER BY Ssn

As soon as you try to add another column to the output, that's when you run in to problems. With a GROUP BY query, you can only return columns in the select list if it appears in the GROUP BY list or it is included in an aggregate function (like Min, Max, etc...).

For your query, I would suggest something list this:

;With MyData As
(
  SELECT My_ID,
         Ssn,
         MY_Date,
         ROW_NUMBER() Over (Partition By SSN Order By My_Date DESC) As RowID
  FROM   My_Table
)
Select MY_ID, SSN, My_Date
From   MyData
Where  RowID = 1

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pdbowling]

Thank you so much. That is quite an elegant solution.