CODEWITHSUNDEEP
sqlsql-servert-sql
Jeff Stock
Jeff Stock

Reputation: 3856

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

Upvotes: 2

Views: 2854

Answers (1)

SQLMenace
SQLMenace

Reputation: 135111

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

Upvotes: 8

Related Questions