I am attempting to resolve the following.

I have two tables looking at membership within different stores and customers who have membership with these stores and how much points they had saved up. Each customer may be a member to multiple stores and their membership points for these stores varies. Both tables are linked by the personID. I currently have a code set up that takes the name of the stores from table a, the name of the customer from table b and the sum of the points the customer has from column b. So it looks something like this:

SELECT A.storename, B.lastname, SUM(B.memberpoints) AS 'memberpoints'
FROM
Storetable AS A
JOIN
Customertable AS B
ON
A.personID = B.personID
GROUP BY A.storename,B.lastname
ORDER BY memberpoints DESC

What I got was a table that shows me a list of the stores with the customers and for each of them the one with the most amount of points for that customer for that store. So something like this:

What I have been trying to do is only show each store's member that has the most points whilst omitting the rest so for example for ASDA only Smith will be shown, for Tesco only Johns etc I have been trying to see how I can use the MAX function or a subquery but it has not worked thus far.

CodePudding user response：

You can use the window function ROW_NUMBER for that, but you need also a CTE to select the customer with the highest number

It will not get you multiple customer with the higHest poInts, for that you need RANK or DENSE_RAnk

WITH CTE AS 
(SELECT St.storename, Cu.lastname, SUM(Cu.memberpoints) AS [memberpoints]
, ROW_NUMBER() OVER(PARTITION BY St.storename ORDER BY SUM(Cu.memberpoints) DESC) rn
FROM
Storetable AS St
JOIN
Customertable AS Cu
ON
St.personID = Cu.personID
GROUP BY St.storename,Cu.lastname)
SELECT storename,lastname,memberpoints FROM CTE WHERE rn = 1

storename | lastname | memberpoints
:-------- | :------- | -----------:
ASDA      | Smith    |         8000
M&S       | Jacobs   |         6000
Tesco     | Smith    |         8000

db<>fiddle here