CODEWITHSUNDEEP
sqlms-access
Joe
Joe

Reputation: 311

How to sort the query result by the number of specific column in ACCESS SQL?

For example:

This is the original result

Alpha      Beta
A             1
B             2
B             3
C             4

After Order by the number of Alpha, this is the result I want

Alpha      Beta
B             2
B             3
A             1
C             4

I tried to use GroupBy and OrderBy, but ACCESS always ask me to include all columns.

Upvotes: 1

Views: 64

Answers (2)

Serg
Serg

Reputation: 22811

The query counts number of rows for every distinct Alpha and sorts. General Sql, tweak for ACCESS if needed.

SELECT t1.alpha,t1.beta 
FROM  t t1
JOIN ( 
  SELECT t2.alpha, count(t2.*) AS n FROM t t2 GROUP BY t2.alpha
) t3 ON t3.alpha = t1.alpha
ORDER BY t3.n, t1.alpha, t1.beta

Upvotes: 1

sagi
sagi

Reputation: 40481

Why is 'B' placed before 'A' ? I don't understand this order..

Any way, doesn't seem like you need a group by, not from your data sample, but for your desired result you can use CASE EXPRESSION :

SELECT t.alpha,t.beta FROM YourTable t
ORDER BY CASE WHEN t.alpha = 'B' THEN 1 ELSE 0 END DESC,
         t.aplha, 
         t.beta

EDIT: Use this query:

SELECT t.alpha,t.beta FROM YourTable t
INNER JOIN(SELECT s.alpha,count(*) as cnt
           FROM YourTable s 
           GROUP BY s.alpha) t2
 ON(t.aplha = t2.alpha)
ORDER BY t2.cnt,t.alpha,t.beta

Upvotes: 1

Related Questions