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

Question

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

what version of SQL Server are you using?

Lamak
– Lamak

2011-04-12 20:48:37 +00:00
Commented Apr 12, 2011 at 20:48 — Lamak
– Lamak, Commented Apr 12, 2011 at 20:48

SQLMenace · Accepted Answer · 2011-04-12 22:33:14Z

8

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

edited Apr 12, 2011 at 22:33

answered Apr 12, 2011 at 20:48

SQLMenace

136k25 gold badges212 silver badges227 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jeff Stock Over a year ago

I don't quite understand the second way with the common table expression because it doesn't even have the table name in it.

SQLMenace Over a year ago

That is because I forgot to add it, I have added it now

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related