RE: group by sorting

From: Bowes, Chris <Chris.Bowes_at_kosa.com>
Date: Wed, 18 Oct 2000 10:06:47 -0400
Message-Id: <10653.119641@fatcity.com>

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_at_Kosa.com

-----Original Message-----
From: Ron Rogers [mailto:RROGERS_at_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_at_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_at_Kosa.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Ron Rogers
  INET: RROGERS_at_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_at_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

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2650.12">
<TITLE>RE: group by sorting</TITLE>
</HEAD>
<BODY>

<P><FONT SIZE=3D2>Hello everyone,</FONT>
</P>

<P><FONT SIZE=3D2>&nbsp;&nbsp;&nbsp; Talk about =
embarrassing...&nbsp;&nbsp; I want to apologize for that last =
question.&nbsp; It was so incomplete that it wasn't worth asking.&nbsp; =
It's what I get for typing as I pack my briefcase to go =
home...</FONT></P>

<P><FONT SIZE=3D2>The query in question is part of a subquery and the =
order by in the subquery won't be allowed.&nbsp; What the user is =
trying to do is get the last 30 rows of a subquery grouping.&nbsp; I =
tried something similiar to this:</FONT></P>

<P><FONT SIZE=3D2>select start_date, cnt from (</FONT>
<BR><FONT SIZE=3D2>select start_date, count(8) &quot;CNT&quot; from =
machine_lineup</FONT>
<BR><FONT SIZE=3D2>group by start_date) where rownum &lt; 30 </FONT>
<BR><FONT SIZE=3D2>order by start_date desc</FONT>
</P>

<P><FONT SIZE=3D2>and all it did was give me the the first 30 rows =
reverse sorted instead of the last 30 rows.&nbsp; This isn't the actual =
query.&nbsp; It's 2 pages long, but it is what our developer wants to =
do in the query she is writing.</FONT></P>

<P><FONT SIZE=3D2>Again I am sorry I worded that question so =
badly.&nbsp; Any further help would be appreciated.&nbsp; Thank =
you.</FONT>
</P>

<P><FONT SIZE=3D2>--Chris</FONT>
<BR><FONT SIZE=3D2>Chris.Bowes_at_Kosa.com</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: Ron Rogers [<A =
HREF=3D"mailto:RROGERS_at_galottery.org">mailto:RROGERS_at_galottery.org</A>]<=
/FONT>
<BR><FONT SIZE=3D2>Sent: Wednesday, October 18, 2000 9:56 AM</FONT>
<BR><FONT SIZE=3D2>To: Multiple recipients of list ORACLE-L</FONT>
<BR><FONT SIZE=3D2>Subject: Re: group by sorting</FONT>
</P>
<BR>

<P><FONT SIZE=3D2>How about adding an order by clause with the group by =
clause?</FONT>
<BR><FONT SIZE=3D2>ROR</FONT>
</P>

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

<P><FONT SIZE=3D2>--Chris</FONT>
<BR><FONT SIZE=3D2>Chris.Bowes_at_Kosa.com </FONT>
</P>
<BR>

<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Please see the official ORACLE-L FAQ: <A =
HREF=3D"http://www.orafaq.com" =
TARGET=3D"_blank">http://www.orafaq.com</A></FONT>
<BR><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Author: Ron Rogers</FONT>
<BR><FONT SIZE=3D2>&nbsp; INET: RROGERS_at_galottery.org</FONT>
</P>

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

Received on Wed Oct 18 2000 - 09:06:47 CDT