returning max value of one column per cust and returning multiple columns

Hey guys,

I have a view with four columns: CustID, DocNum, OppNum, Date.  I want to query this view and return the row with the latest date for each CustID.  I want to return all 4 columns in the query.  I tried using max(date) but no luck.  Any help you can provide is much appreciated.  Thank you.
LVL 2
mike247Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
SELECT CustID, DocNum, OppNum, Date
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY CustID ORDER BY Date DESC) AS row_num
    FROM dbo.table_name
) AS derived
WHERE row_num = 1
--ORDER BY CustID
0
 
mike247Author Commented:
This works perfectly.  Thanks!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.