Re: ORDER BY on nested queries
From: arkarrel <carrollusions_at_gmail.com>
Date: Thu, 18 Jun 2009 08:55:07 -0700 (PDT)
Message-ID: <677d3003-d64f-4dcd-ba02-b7807c9e1ca3_at_k19g2000prh.googlegroups.com>
On Jun 17, 1:08 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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_t...
>
> jg
> --
> _at_home.com is bogus.http://netzpolitik.org/2009/the-dawning-of-internet-censorship-in-ger...
Date: Thu, 18 Jun 2009 08:55:07 -0700 (PDT)
Message-ID: <677d3003-d64f-4dcd-ba02-b7807c9e1ca3_at_k19g2000prh.googlegroups.com>
On Jun 17, 1:08 pm, joel garry <joel-ga..._at_home.com> wrote:
> 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_t...
>
> jg
> --
> _at_home.com is bogus.http://netzpolitik.org/2009/the-dawning-of-internet-censorship-in-ger...
Not looking for the top 10 rows. I simply want to be able to order the entire result by certain fields generated by the query. So that I can reorder/sort by Most Sites, Most Announcements, etc or do an order by desc on all, and get the top results based on the counts. Received on Thu Jun 18 2009 - 10:55:07 CDT