Site Sponsored By: SQLDSC - SQL Server Desired State Configuration
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.
Hi,I need a report that shows the last day that a client was billed and also the rep name that billed the client. I know how to do the last billed date using MAX, but not sure how to bring the name too.My fields are:Client, date, RepTable TSo basically I need only one line per client, showing the last date and the Rep name.ANy help would be appreciated.Thanks!
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts
Posted - 2012-12-27 : 09:45:38
If you have just one client for whom you want to get the data
SELECT TOP (1) Client, Date, Rep FROM Tbl tWHERE Client = 'JaneDoe'ORDER BY Date DESC
For all clients
SELECT Client, Date, RepFROM( SELECT *, ROW_NUMBER() OVER (PARTITION BY Client ORDER BY Date DESC) AS RN FROM Tbl)sWHERE RN = 1;