Re: ORDER BY on nested queries
From: joel garry <joel-garry_at_home.com>
Date: Thu, 18 Jun 2009 09:50:57 -0700 (PDT)
Message-ID: <3d456ce4-233f-4471-a74b-45481d3440cb_at_w35g2000prg.googlegroups.com>
On Jun 18, 8:55�am, arkarrel <carrollusi..._at_gmail.com> wrote:
> 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.
Date: Thu, 18 Jun 2009 09:50:57 -0700 (PDT)
Message-ID: <3d456ce4-233f-4471-a74b-45481d3440cb_at_w35g2000prg.googlegroups.com>
On Jun 18, 8:55�am, arkarrel <carrollusi..._at_gmail.com> wrote:
> 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.
If you want more detailed help, you would be better off giving DDL and sample data so people can replicate what you are trying to do.
But this might help, if your problem is getting to the right level of detail in the query: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:995030557145
There are a number of other possible answers to ordering by certain generated fields. Maybe even http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_sql.html which makes my brain explode.
jg
-- _at_home.com is bogus. http://www.itbusiness.ca/it/client/en/home/News.asp?sub=true&id=53500Received on Thu Jun 18 2009 - 11:50:57 CDT