Re: ORDER BY on nested queries

From: joel garry <joel-garry_at_home.com>
Date: Wed, 17 Jun 2009 13:08:15 -0700 (PDT)
Message-ID: <967b18fe-18a1-461e-b044-ab410bd8a7c4_at_r37g2000yqd.googlegroups.com>



On Jun 17, 10:33 am, arkarrel <carrollusi..._at_gmail.com> wrote:
> Hello,
>
> 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

Didn't look too close, 'cause it sounds like a faq: http://www.orafaq.com/faq/how_does_one_select_the_top_n_rows_from_a_table

jg

--
_at_home.com is bogus.
http://netzpolitik.org/2009/the-dawning-of-internet-censorship-in-germany/
Received on Wed Jun 17 2009 - 15:08:15 CDT

Original text of this message