ORDER BY on nested queries

I've written a query to get me the metrics (record counts) in the system.

SELECT sites.total, main.mains, sub.subs, ann.announcements, ass.assignments, res.resources, pc.channels, pi.podcasts, loc.locations, u.users FROM

(SELECT count(site_pk) total FROM site) sites,
(SELECT count(site_pk) mains FROM site WHERE mainsite_id = site_id)
main,

(SELECT count(site_pk) subs FROM site WHERE mainsite_id != site_id)
sub,

(SELECT count(announce_pk) announcements FROM announcements) ann,
(SELECT count(assign_pk) assignments FROM assignments) ass,
(SELECT count(resource_pk) resources FROM resources) res,
(SELECT count(podcast_channel_pk) channels FROM podcast_channel) pc,
(SELECT count(podcast_items_pk) podcasts FROM podcast_items) pi,
(SELECT count(distinct(location_code)) locations FROM site) loc,
(SELECT count(tracking_id) users FROM users) u

I then modified that to give me the counts per user.

SELECT u.tracking_id, u.full_name,
  (SELECT site_id FROM site WHERE tracking_id = u.tracking_id AND site_id = mainsite_id) mainsite,
  (SELECT count(site_pk) total FROM site WHERE tracking_id = u.tracking_id) sites,
  (SELECT count(announce_pk) announcements FROM announcements WHERE tracking_id = u.tracking_id) announcements,

(SELECT count(assign_pk) assignments FROM assignments WHERE
tracking_id = u.tracking_id) assignments,

(SELECT count(resource_pk) resources FROM resources WHERE tracking_id
= u.tracking_id) resources,

(SELECT count(podcast_channel_pk) channels FROM podcast_channel WHERE
tracking_id = u.tracking_id) channels,

(SELECT count(podcast_items_pk) podcasts FROM podcast_items WHERE
tracking_id = u.tracking_id) podcasts
FROM users u

Now I'm trying to ORDER that query so that I can get the top users for the record counts of sites, announcements, assignments, etc.

The following query returns the same as above.

SELECT x.tracking_id, x.full_name, x.mainsite, x.sites, x.announcements, x.assignments, x.resources, x.channels, x.podcasts FROM
(SELECT u.tracking_id, u.full_name,
  (SELECT site_id FROM site WHERE tracking_id = u.tracking_id AND site_id = mainsite_id) mainsite,
  (SELECT count(site_pk) total FROM site WHERE tracking_id = u.tracking_id) sites,
  (SELECT count(announce_pk) announcements FROM announcements WHERE tracking_id = u.tracking_id) announcements,

(SELECT count(assign_pk) assignments FROM assignments WHERE
tracking_id = u.tracking_id) assignments,

(SELECT count(resource_pk) resources FROM resources WHERE tracking_id
= u.tracking_id) resources,

(SELECT count(podcast_channel_pk) channels FROM podcast_channel WHERE
tracking_id = u.tracking_id) channels,

(SELECT count(podcast_items_pk) podcasts FROM podcast_items WHERE
tracking_id = u.tracking_id) podcasts
FROM users u) x

If I add

ORDER BY x.sites ASC

I get 'ORA-01427: single-row subquery returns more than one row'

    Cause: The outer query must use one of the keywords ANY, ALL, IN, or NOT IN to specify values to compare because the subquery returned more than one row.

    Action: Use ANY, ALL, IN, or NOT IN to specify which values to compare or reword the query so only one row is retrieved.

I'm not sure how or where to use ANY,ALL,IN etc in the syntax and everything I've tried isn't working.

Thanks Received on Wed Jun 17 2009 - 12:33:26 CDT