Max() on one column but display value of different

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Max() on one column but display value of different

Author

Topic

edyl
Starting Member

35 Posts

Posted - 2011-08-02 : 17:32:11

Hello Everyone,

Please see the following table example. I want create a SQL qurey where I would like a row for each Customer with "CustID" and "Update Date" in the output where status is 1 and is the most recently "Audited" (max(AuditDate)).

Customer Table:


PKCust---CustID---Customer---UpdateDate----AuditDate---Status
1--------10100----xyz--------6/1/2011------6/5/2011----0
2--------10100----xyz--------6/2/2011------6/7/2011----0
3--------10100----xyz--------6/2/2011------6/6/2011----1
4--------10100----xyz--------6/1/2011------6/7/2011----1
5--------10200----abc--------6/10/2011-----7/1/2011----1
6--------10200----abc--------6/10/2011-----7/2/2011----1
7--------10200----abc--------6/11/2011-----7/3/2011----0
8--------10200----abc--------6/11/2011-----7/3/2011----0

So in other words I like to know each Customer's UpdateDate when it was most recenly audited and whose status is 1.

The result I am looking for is:

CustID---UpdateDate----[AuditDate]---[Status]
10100----6/1/2011------[6/7/2011]----[1]
10200----6/10/2011-----[7/2/2011]----[1]

Audit date and Status are not required in the result set, but I am including here to make it more clear.

I Tried:

select CustID, UpdateDate, max(AuditDate)
from Customer
where Status = 1
group by CustID, UpdateDate

But since this query needs UpdateDate in the Group By clause, it will also roll up the results by UpdateDate thus giving more rows than needed.

So I guess it boils down to something like this, I want to get the row which has the max(AuditDate) but display the UpdateDate that is in that row along with the CustID

How do we do this?

Any ideas or suggestions would be greatly apprecieated.

Thanks in Advance.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-08-02 : 17:44:29

If you are on SQL 2005 or higher, row_number() function would let you do this, like this:

;WITH cte AS
(
	SELECT
		*,
		ROW_NUMBER() OVER (PARTITION BY CustID ORDER BY AuditDate DESC) AS RN
	FROM
		CustomerTable
)
SELECT * FROM cte WHERE RN = 1;

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2011-08-02 : 17:45:48

One way:

SELECT CustID, UpdateDate, AuditDate
FROM
(select CustID, UpdateDate, AuditDate, Row_Number() OVER (Partition by CustID ORDER BY AuditDate DESC) as RowNo
from Customer
where Status = 1) Sub
Where RowNo = 1

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-03 : 01:00:37

Another way


SELECT c.CustID,c1.UpdateDate,c1.[AuditDate],c1.[Status]
FROM (SELECT DISTINCT CustID FROM Customer) c
CROSS APPLY (SELECT TOP 1 UpdateDate,[AuditDate],[Status]
             FROM Customer
             WHERE CustID = c.CustID
             ORDER BY [AuditDate] DESC)c1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

edyl
Starting Member

35 Posts

Posted - 2011-08-03 : 11:58:27

Thanks guys, using the row_number() of the sorted out result set works perfect.

Thanks for the help and a prompt response.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources