How to return multiple columns of the same row in T-SQL using GROUP BY

I have a Transactions table that contains the following columns: MemberID, TransactionID, Date, MethodOfPayment, + a lot more columns

The primary key consists of MemberID and TransactionID. I need a query that groups these rows by MemberID for the latest date. That's easy, but additionally I need the rest of开发者_运维问答 the columns for the latest date, such as MethodOfPayment. I'm looking for the simplest way to accomplish this.

I know this is one way I could do it, but the query gets really long if I have to include a subquery for every column I have. My gut tells me there has to be a better way.

SELECT
   MemberID,
   MAX(Date) AS Date,
   (
      SELECT TOP(1) MethodOfPayment
      FROM Transactions
      WHERE MemberID = t.MemberID
      ORDER BY Date DESC
   ) AS MethodOfPayment
FROM Transactions t
GROUP BY MemberID

---

One way

SELECT t1.* 
FROM(
SELECT
   MemberID,
   MAX(Date) AS MaxDate
   FROM Transactions 
GROUP BY MemberID) t2 
JOIN Transactions t1 ON t2.MaxDate = t1.Date
AND t2.MemberID = t1.MemberID

Another way if you are on SQL Server 2005 or up

;WITH  cte AS(SELECT *,
 ROW_NUMBER() OVER(PARTITION BY MemberID ORDER BY date DESC) AS ROW
 FROM Transactions)

SELECT * FROM cte
where row = 1