Message-Id: <10653.119641@fatcity.com> From: "Bowes, Chris" Date: Wed, 18 Oct 2000 10:06:47 -0400 Subject: RE: group by sorting This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C0390C.A8FF1DBA Content-Type: text/plain; charset="iso-8859-1" Hello everyone, Talk about embarrassing... I want to apologize for that last question. It was so incomplete that it wasn't worth asking. It's what I get for typing as I pack my briefcase to go home... The query in question is part of a subquery and the order by in the subquery won't be allowed. What the user is trying to do is get the last 30 rows of a subquery grouping. I tried something similiar to this: select start_date, cnt from ( select start_date, count(8) "CNT" from machine_lineup group by start_date) where rownum < 30 order by start_date desc and all it did was give me the the first 30 rows reverse sorted instead of the last 30 rows. This isn't the actual query. It's 2 pages long, but it is what our developer wants to do in the query she is writing. Again I am sorry I worded that question so badly. Any further help would be appreciated. Thank you. --Chris Chris.Bowes@Kosa.com -----Original Message----- From: Ron Rogers [mailto:RROGERS@galottery.org] Sent: Wednesday, October 18, 2000 9:56 AM To: Multiple recipients of list ORACLE-L Subject: Re: group by sorting How about adding an order by clause with the group by clause? ROR >>> Chris.Bowes@kosa.com 10/17/00 06:22PM >>> I have looked in a couple manuals for this but have not found it. When doing a group by, a sort is done on the data. Is there anyway to specify a descending order for that sort? It is defaulted to ascending. Is there a way of overriding that? I told the developer that she should put it into a subquery but the query itself (all 3 pages) is already complex enough. Any ideas? Thank you in advance. --Chris Chris.Bowes@Kosa.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers INET: RROGERS@galottery.org Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C0390C.A8FF1DBA Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable RE: group by sorting

Hello everyone,

    Talk about = embarrassing...   I want to apologize for that last = question.  It was so incomplete that it wasn't worth asking.  = It's what I get for typing as I pack my briefcase to go = home...

The query in question is part of a subquery and the = order by in the subquery won't be allowed.  What the user is = trying to do is get the last 30 rows of a subquery grouping.  I = tried something similiar to this:

select start_date, cnt from (
select start_date, count(8) "CNT" from = machine_lineup
group by start_date) where rownum < 30
order by start_date desc

and all it did was give me the the first 30 rows = reverse sorted instead of the last 30 rows.  This isn't the actual = query.  It's 2 pages long, but it is what our developer wants to = do in the query she is writing.

Again I am sorry I worded that question so = badly.  Any further help would be appreciated.  Thank = you.

--Chris
Chris.Bowes@Kosa.com


-----Original Message-----
From: Ron Rogers [mailto:RROGERS@galottery.org]<= /FONT>
Sent: Wednesday, October 18, 2000 9:56 AM
To: Multiple recipients of list ORACLE-L
Subject: Re: group by sorting


How about adding an order by clause with the group by = clause?
ROR

>>> Chris.Bowes@kosa.com 10/17/00 06:22PM = >>>
I have looked in a couple manuals for this but have = not found it.  When
doing a group by, a sort is done on the data. Is = there anyway to specify a
descending order for that sort?  It is = defaulted to ascending.  Is there a
way of overriding that?   I told the = developer that she should put it into a
subquery but the query itself (all 3 pages) is = already complex enough.  Any
ideas?  Thank you in advance.


--Chris
Chris.Bowes@Kosa.com


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ron Rogers
  INET: RROGERS@galottery.org

Fat City Network Services    -- (858) = 538-5051  FAX: (858) 538-5051
San Diego, = California        -- Public Internet = access / Mailing Lists
---------------------------------------------------------------= -----
To REMOVE yourself from this mailing list, send an = E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of = 'ListGuru') and in
the message BODY, include a line containing: UNSUB = ORACLE-L
(or the name of mailing list you want to be removed = from).  You may