CODEWITHSUNDEEP
sql
NARU
NARU

Reputation: 2829

use count in sql

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!!

Upvotes: 1

Views: 624

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 753585

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;

Upvotes: 4

Derek
Derek

Reputation: 23228

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

Upvotes: 3

Tomalak
Tomalak

Reputation: 338158

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

Upvotes: 4

Related Questions