SELECT 
	*
FROM
	(
		SELECT
			*,
			ROW_NUMBER() OVER (PARTITION BY CustomerNumber ORDER BY cust_date DESC) AS RowNum
		FROM
			cust_data
	) AS T
WHERE
	RowNum = 1


-- OR

SELECT
	*
FROM
	cust_data
INNER JOIN
	(
		SELECT CustomerNumber, MAX(cust_date) AS cust_date
		FROM cust_data
		GROUP BY CustomerNumber
	) AS T
	ON cust_data.CustomerNumber = T.CustomerNumber
	AND cust_data.cust_date = T.cust_date

quote:

---

Originally posted by jassie

In t-sql 2012, I have a table that I need to join to itself server items based upon customer number, cust_date, and attribute id.
The attrribute id values are 53, 54, and 55. There are the same attribute ids that can occur lots of times during the year so the cust_date can change.
I need to join rows in the table to itself several times where the cust_date is the same and the most current date.

Thus can you show me how to join the table to itself with selecting the maximum cust_date and the for attribute value = 53, 54, and 55?

---

SELECT CustomerNumber,
MAX(CASE WHEN AttributeID = 53 THEN Cust_Date END) AS [LatestDate53],
MAX(CASE WHEN AttributeID = 54 THEN Cust_Date END) AS [LatestDate54],
MAX(CASE WHEN AttributeID = 55 THEN Cust_Date END) AS [LatestDate55]
FROM
	(
		SELECT
			*,
			ROW_NUMBER() OVER (PARTITION BY CustomerNumber,AttributeID ORDER BY cust_date DESC) AS RowNum
		FROM
			cust_data
	) AS T
WHERE
	RowNum = 1
GROUP BY CustomerNumber