use count in sql

Question

I need a SQL statement, the requirement is: there is a table, which has two columns: ID, Owner_ID; I inserted several records, for example:

ID    Owner_ID
0         0
1         0
2         1
3         1
4         2
5         3
6         3
7         3
8         3
9         0

Now I need a SQL statement, which returns a ID list sorted by the number of rows owned by different user, from largest to smallest. In this example, owner 3 has four rows; owner 0 has three rows, owner 1 has two rows; and owner 2 has one rows. the result should be

ID    Owner_ID
5         3
6         3
7         3
8         3
0         0
1         0
9         0
2         1
3         1
4         2

I think I should use the aggregate function count, does anybody have an idea?

I am using HSQLDB.

Thanks in advance!!

Which DBMS? Generating the result in pure SQL as shown requires a DBMS that allows you to ORDER BY a (computed) column that is not present in the select-list, which is not standard SQL functionality. — Jonathan Leffler
– Jonathan Leffler, Commented Sep 6, 2011 at 15:30

Jonathan Leffler · Accepted Answer · 2011-09-06 15:34:07Z

4

This will work with most SQL DBMS, but shows the count value.

SELECT ID, Owner_ID, Owner_Count
  FROM AnonymousTable AS A
  JOIN (SELECT Owner_ID, COUNT(*) AS Owner_Count
          FROM AnonymousTable
         GROUP BY Owner_ID
       ) AS B ON B.Owner_ID = A.Owner_ID
 ORDER BY Owner_Count DESC, Owner_ID ASC, ID ASC;

This will work with some, but not necessarily all, DBMS; it orders by a column that is not shown in the result list:

SELECT ID, Owner_ID
  FROM AnonymousTable AS A
  JOIN (SELECT Owner_ID, COUNT(*) AS Owner_Count
          FROM AnonymousTable
         GROUP BY Owner_ID
       ) AS B ON B.Owner_ID = A.Owner_ID
 ORDER BY Owner_Count DESC, Owner_ID ASC, ID ASC;

answered Sep 6, 2011 at 15:34

Jonathan Leffler

759k145 gold badges961 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tomalak · Accepted Answer · 2011-09-06 15:29:12Z

4

This is very straight-forward.

SELECT
  ID,
  Owner_ID
FROM
  TheTable t
ORDER BY
  (SELECT COUNT(*) FROM TheTable i WHERE i.Owner_ID = t.Owner_ID) DESC

answered Sep 6, 2011 at 15:29

Tomalak

339k68 gold badges547 silver badges635 bronze badges

2 Comments

Andy Over a year ago

Or perhaps "ORDER BY (SELECT COUNT(*) FROM TheTable i WHERE i.Owner_ID = t.Owner_ID) DESC, Owner_ID, ID"?

Tomalak Over a year ago

@Andy: If you want to. The OP did not specify any intra-group sorting, but that should be easy enough to figure out. :)

Derek · Accepted Answer · 2011-09-06 15:29:58Z

3

Yes, you have the general idea. Try the following query:

select t.ID, t.Owner_ID
from table t
inner join (select Owner_ID, count(*) c from table t group by Owner_ID) tc
  on t.Owner_ID = tc.Owner_ID
order by
  tc.c desc, t.ID asc

answered Sep 6, 2011 at 15:29

Derek

23.5k8 gold badges55 silver badges61 bronze badges

Collectives™ on Stack Overflow

use count in sql

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related