Get first occurrence in a sorted list

[Sharkie21]

Trying to get the first record for each member id in my sorted list.

SELECT [member_id]
,[physician_id]
,[most_recent_date]
,[num_visits]
FROM table1
order by [member_id] asc, num_visits desc, [most_recent_date] desc

The table contains a list of members, the physician(s) they seen, date of the most recent visit with that particular physician, and how many times they saw that physician.

Goal is to determine which physician is responsible for that patient based on the following logic. 1) Which ever physician the patient visited the most is responsible, if tied then 2) which ever physician the patient most recently visited is responsible.

I have my list sorted, such that the top entry for each member id would be the physician responsible, how do I carve that out so I only have a single entry for each member with my physician information (physician_id, most recent date, num_visits)?

 

[Joe4]

I was able to do it in a series of two queries.

The first query I named "Query1" and here is the code I used:

SELECT Table1.member_id, Max(Table1.most_recent_date) AS MaxOfmost_recent_date, Table1.num_visits, [num_visits]=DMax("[num_visits]","Table1","[member_id]='" & [member_id] & "'") AS Max_Check
FROM Table1
GROUP BY Table1.member_id, Table1.num_visits, [num_visits]=DMax("[num_visits]","Table1","[member_id]='" & [member_id] & "'")
HAVING ((([num_visits]=DMax("[num_visits]","Table1","[member_id]='" & [member_id] & "'"))=True));

And here is the second query, which returns each member once, along with the details of their responsible physician:

SELECT Table1.member_id, Table1.physician_id, Table1.most_recent_date, Table1.num_visits
FROM Query1 INNER JOIN Table1 
ON (Query1.MaxOfmost_recent_date = Table1.most_recent_date) AND (Query1.num_visits = Table1.num_visits) AND (Query1.member_id = Table1.member_id)
ORDER BY Table1.member_id;

 

[Sharkie21]

Thank you Joe. Is there any way without using DMax? I'm using MS SQL Server, and it doesn't recognize that function.

 

[Joe4]

You could do a separate Aggregate Query, which gets the Max record_count for each member_id, and link that in instead of using DMAX.