Select statement Query SQL

shpresa
shpresa used Ask the Experts™
on
Hi Experts,
I need to create this report in sql by selecting the last record for each referralID. As you see below in the table i have the same referralID many times and i need to select 1 record per each referralID. That would be the last one entered, or the one with highest number of "StatusUpdateID". Suggestions?

Here is how the table looks like.
StatusUpdateID          ReferralID         Q1         Q2             Q3
1                                    685                      1           1                 2
2                                    685                      1            1                 1
3                                    685                      1           0                 0
4                                    688                       0           0                 1
5                                    688                      1            1                 1
6                                    689                      1              1               1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
SELECT *
FROM (
    SELECT *,
        ROW_NUMBER() OVER(PARTITION BY referralID ORDER BY StatusUpdateID DESC) AS row_num
    FROM table_name
) AS derived
WHERE row_num = 1

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial