SQL Group results by column then order most instances to least-CodePudding

I need to return all m_id where cli_id have false cli_billstat. then I need to sort by largest count of a cli_id to fewest.

My query returns the info I need but I cannot get it to by the cli_id the way I want it to.

My code:

SELECT CLIENT.CLI_ID, CLI_FNAME, CLI_LNAME, CLI_BILLSTAT, M_ID, M_DATE
FROM CLIENT, MEETING
WHERE CLIENT.CLI_ID = MEETING.CLI_ID
     AND CLI_BILLSTAT = False
GROUP BY CLIENT.CLI_ID, CLI_FNAME, CLI_LNAME, CLI_BILLSTAT, M_ID, M_DATE\\\

in the cli_id column it returns
cli_id
10
10
14
21
21
21

what I need is something like:
cli_id
21
21
21
10
10
14
22

ive tried adding COUNT(client.cli_id) AS COUNT then ORDER BY COUNT, but it does nothing to change the result.

CodePudding user response：

You have to use a subquery, essentially repeating the join query, as follows:

select 
    S1.CLI_ID,
    S1.CLI_FNAME,
    S1.CLI_LNAME, 
    S1.CLI_BILLSTAT, 
    S1.M_ID,
    S1.M_DATE,
    S1.Cnt
from 
  (SELECT ClntA.CLI_ID,
          ClntA.CLI_FNAME,
          ClntA.CLI_LNAME, 
          ClntA.CLI_BILLSTAT, 
          MtgA.M_ID,
          MtgA.M_DATE,
         (select count(*) 
          from CLIENT ClntB 
               Inner Join 
               MEETING MtgB 
               on ClntB.CLI_ID = MtgB.CLI_ID
          WHERE ClntB.CLI_BILLSTAT = False
             AND ClntB.cli_id=ClntA.cli_id) as Cnt
   FROM
        CLIENT ClntA 
        Inner Join 
        MEETING MtgA 
        on ClntA.CLI_ID = MtgA.CLI_ID
   WHERE ClntA.CLI_BILLSTAT = False
 ) S1
order by S1.cnt desc, S1.cli_id;

In most other modern dbms products this can be achieved with windowing functions, but MsAccess SQL lacks them.

You can shorten the subquery significantly if CLI_ID is the primary key of the CLIENT table. If this is the case, the subquery can only count records from MEETING with MtgB.CLI_ID=ClntA.CLI_ID (no need for the JOIN or WHERE CLI_BILLSTAT in the subquery)