# Using MAX function with GROUP By Function

Experts:

I have a SQL query that shows the number of times a customer shops at a particular store.  I need a way to modify this query so that it returns only the one store where the customer shops the most.

Below is my query:

SELECT CUSTNUM, STORENUM, COUNT(STORENUM) AS FAV FROM CUSTSLS,CUSTOMER
WHERE CUSTOMER.CUSTOMERNUM=CUSTSLS.CUSTNUM
AND NOT (CUSTNUM IN (1,2,3,4)
AND CUSTSLS.FORMAT='D'
AND MASTERCUSTNUM=0
AND ACCOUNTTYPE IN ('A','B','C','D')
AND (DEPT<70 OR DEPT IN (76,78))
GROUP BY CUSTNUM,STORENUM
ORDER BY CUSTNUM,FAV DESC

This essentially returns data similar to the following:

10     1     5
10     2     3
10     4     2
20     4     5
20     1     2
30     4     4
30     2     1
40     1     8
40     4     5
40     2     1

Now I would really like to boil this down to the following results:

10     1     5
20     4     5
30     4     4
40     1     8

Any suggestions on how I can modify my query to do this?  Thanks!!!
Rey Obrero (Capricorn1)

try this

Select T.CustNum, T.Storenum,max(T.Fav)
FROM
(SELECT CUSTNUM, STORENUM, COUNT(STORENUM) AS FAV FROM CUSTSLS,CUSTOMER
WHERE CUSTOMER.CUSTOMERNUM=CUSTSLS.CUSTNUM
AND NOT (CUSTNUM IN (1,2,3,4)
AND CUSTSLS.FORMAT='D'
AND MASTERCUSTNUM=0
AND ACCOUNTTYPE IN ('A','B','C','D')
AND (DEPT<70 OR DEPT IN (76,78))
GROUP BY CUSTNUM,STORENUM
ORDER BY CUSTNUM,FAV DESC) As T
Group by T.CustNum, T.Storenum

Not exactly working as I had hoped.  Here are the results that the SQL statement pulled:

10        4             5
47        1             2
47        3             1
47        4             1
56        3             1
56        4             1
65        3             3
83        3             2
83        4             4
109        3             1
109        4             1
136        3             2
136        4             1
upload a sample db with the table
Is this MSSQL or Access, since you have put it in both topic areas? With MSSQL you can do like so:
``````Select T.CustNum, T.Storenum, T.Fav
FROM
(SELECT CUSTNUM, STORENUM, COUNT(STORENUM) AS FAV FROM CUSTSLS,CUSTOMER,
rn=row_number() over (partition by custnum, storenum order by count(storenum) desc)
WHERE CUSTOMER.CUSTOMERNUM=CUSTSLS.CUSTNUM
AND NOT (CUSTNUM IN (1,2,3,4)
AND CUSTSLS.FORMAT='D'
AND MASTERCUSTNUM=0
AND ACCOUNTTYPE IN ('A','B','C','D')
AND (DEPT<70 OR DEPT IN (76,78))
GROUP BY CUSTNUM,STORENUM
) As T
where rn = 1``````